question

I used a CTE with all of the desired months and then did a left join from that to your data. For future reference, please use the code formatter when including code samples in your question. Thanks. You should just need the first CTE, then wrap your code in the second CTE, and then include the output SELECT statement at the end. As is, you will get null values for 2ndMonth, but you can easily change that with an ISNULL function in the output statement. Please let us know if this helps. WITH ReportMonths AS ( SELECT * FROM (VALUES ('1stMonth'), ('2ndMonth'),('3rdMonth'),('4thMonth'),('5thMonth'),('6thMonth'),('MoreThan6')) t (Month_Name) ) , TicketData AS ( SELECT u.ID AS PersonID , CASE WHEN DATEDIFF(DAY, CAST(u.CreatedDATE AS DATE) ,CONVERT(DATE, CONVERT(VARCHAR(8), f.DimTktcrtDateKey,112)) ) <= 30 THEN '1stMonth' WHEN DATEDIFF(DAY, CAST(u.CreatedDATE AS DATE),CONVERT(DATE, CONVERT(VARCHAR(8), f.DimTktcrtDateKey,112)) ) > 30 AND DATEDIFF(DAY, CAST(u.CreatedDATE AS DATE),CONVERT(DATE, CONVERT(VARCHAR(8), f.DimTktcrtDateKey,112)) ) <=60 THEN '2ndMonth' WHEN DATEDIFF(DAY, CAST(u.CreatedDATE AS DATE), CONVERT(DATE, CONVERT(VARCHAR(8), f.DimTktcrtDateKey,112)) ) > 60 AND DATEDIFF(DAY, CAST(u.CreatedDATE AS DATE),CONVERT(DATE, CONVERT(VARCHAR(8), f.DimTktcrtDateKey,112)) ) <=90 THEN '3rdMonth' WHEN DATEDIFF(DAY, CAST(u.CreatedDATE AS DATE),CONVERT(DATE, CONVERT(VARCHAR(8), f.DimTktcrtDateKey,112)) ) > 90 AND DATEDIFF(DAY, CAST(u.CreatedDATE AS DATE),CONVERT(DATE, CONVERT(VARCHAR(8), f.DimTktcrtDateKey,112)) ) <=120 THEN '4thMonth' WHEN DATEDIFF(DAY, CAST(u.CreatedDATE AS DATE),CONVERT(DATE, CONVERT(VARCHAR(8), f.DimTktcrtDateKey,112)) ) > 120 AND DATEDIFF(DAY, CAST(u.CreatedDATE AS DATE),CONVERT(DATE, CONVERT(VARCHAR(8), f.DimTktcrtDateKey,112)) ) <=150 THEN '5thMonth' WHEN DATEDIFF(DAY, CAST(u.CreatedDATE AS DATE),CONVERT(DATE, CONVERT(VARCHAR(8), f.DimTktcrtDateKey,112)) ) > 150 AND DATEDIFF(DAY, CAST(u.CreatedDATE AS DATE),CONVERT(DATE, CONVERT(VARCHAR(8), f.DimTktcrtDateKey,112)) ) <=180 THEN '6thMonth' WHEN DATEDIFF(DAY, CAST(u.CreatedDATE AS DATE),CONVERT(DATE, CONVERT(VARCHAR(8), f.DimTktcrtDateKey,112)) ) > 180 THEN 'MoreThan6' END Month_Name , COUNT(*) AS count_Ticket FROM [FactTicket] f INNER JOIN [DimUser] u ON f.DimUserKey = u.DimUserKey WHERE f.[DimTktcrtDateKey] >= 20140301 AND CAST(u.CreatedDATE AS DATE) BETWEEN '1 Mar 2014' AND '31 Mar 2014' AND f.FactTicketCurrentStateFlag = 1 AND ID = 1 GROUP BY u.ID, u.CreatedDATE, f.DimTktcrtDateKey ) SELECT td.PersonID, rm.Month_Name, td.count_Ticket FROM ReportMonths rm LEFT JOIN TicketData td ON td.Month_Name = rm.Month_Name ORDER BY PersonID, Month_Name ;