How I Add All category for all yearmonth wise data?

Viewed 26

Here is my Query:-

;WITH Tenure
AS
(
    SELECT 
        T.[Employee ID],
        CASE WHEN [Datediff_] = logic THEN '0 to 1 Year'
             WHEN [Datediff_] = logic THEN '1 to 1.11 Year'
             WHEN [Datediff_] = logic THEN '2 to 2.11 Year'
             WHEN [Datediff_] = logic THEN '3 to 3.11 Year'
             WHEN [Datediff_] = logic THEN '4 to 4.11 Year' 
             ELSE 'More Than 5 Years' END AS [Tenure Grouping]          
    FROM
    (
        SELECT 
            DISTINCT [Employee ID],[Hire Date],[Last Working Day],
            CASE WHEN logic END AS [Datediff_]
        FROM [dbo].[Employee_]
    )AS T
)

,Headcount
AS
(
    SELECT DISTINCT 
            FORMAT(T1.HireDate,'yyyyMM') AS [Month Year],
            T2.[Tenure Grouping],
            COUNT(T1.[Employee ID]) AS Headcount
        FROM [dbo].[Employee_] AS T1 WITH (NOLOCK)
        LEFT OUTER JOIN Tenure AS T2 WITH (NOLOCK) ON T1.[Employee ID] = T2.[Employee ID]
)


SELECT * FROM Headcount

Result From this Query After RUN:-

YearMonth Tenure Grouping Hiring
202101 0 to 1 Year 65
202101 1 to 1.11 Year 49

But I want this Result:-

YearMonth Tenure Grouping Hiring
202101 0 to 1 Year 65
202101 1 to 1.11 Year 49
202101 2 to 2.11 Year 0
202101 3 to 3.11 Year 0
202101 4 to 4.11 Year 0
202101 More Than 5 0

Note:- For all Year Month Column I need all the category. If there is no value then put '0'.

Is this possible? Thanks in advance.

0 Answers
Related