Misc SQL: Create monthly Allowance report and Fill Null against those month where emp has not received any allowances

Question: Generate a month wise allowance received report for each employee, keep Null in allowance for those month where employee has not received any allowance.

emp_id emp_name month_number allowance

E001 Aayansh         1         1000

E001 Aayansh         2         3000

E002 Rishika         3         2000

E002 Rishika         5         4000

Output should be :

month_number emp_id emp_name allowance

1 E001 Aayansh 1000

2 E001 Aayansh 3000

3 E001 Aayansh null

4 E001 Aayansh null

5 E001 Aayansh null

6 E001 Aayansh null

7 E001 Aayansh null

8 E001 Aayansh null

9 E001 Aayansh null

10 E001 Aayansh null

11 E001 Aayansh null

12 E001 Aayansh null

Solution: 

Steps:

1) generate a month number derived CTE

2) Cross join MonthCTE with EmpAllowance Table and take the distinct record

3) Left join the CorssJoin result from 2nd step to EmpAllowance table and take allowance from EmpAllowance

Query:

;with MonthCTE AS

(select 1 as month_number 

 union all 

 select month_number + 1 as month_number from MonthCTE 

 where month_number < 12

 ),

ReportTemplateCTE as 

(select DISTINCT m.month_number, e.emp_id, e.emp_name from MonthCTE m cross join EmpAllowance e)

 

select r.*, e.allowance from ReportTemplateCTE r left join EmpAllowance e

on r.month_number = e.month_number and r.emp_id = e.emp_id

order by r.emp_id

 

Post Reference: Vikram Aristocratic Elfin Share