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.