Last 24 Months Records -Month Wise Need to fetch from SQL Server

Question

I have an employee table, I need to fetch the total count of employees based on monthly wise for the last two years from the specified month.

Table Design

+-----------+-------------+
| Field     | Type        |   
+-----------+-------------+    | 
| emp_name  | varchar(30) | 
| join_date | date        | 
| emp_id    | int(5)      | 
+-----------+-------------+

If I choose the current month in the drop-down (dec 2022) , I need to show the output below.

+-----------+-------------+
| Month     | Emp.Count   |   
+-----------+-------------+
| Dec 22    | 10          | 
| Nov 22    | 8           | 
| Oct 22    | 3           | 
| ......    | .           | 
| ......    | .           | 
| ......    | .           | 
| Dec 21    | 5           | 
| Nov 21    | 6           | 
| Oct 21    | 7           | 
| Sept 21   | 7           | 
+-----------+-------------+

I tried with the following queries, but the count is added with Jan month (both 2021 and 2022)

SELECT MAX(DATENAME(MM,[join_date])) AS Month, COUNT(1) AS "Total.EMP"
FROM [EMP_TABLE] 
WHERE [join_date] BETWEEN DATEADD(yy, DATEDIFF(yy,0,GETDATE()), 0) AND GETDATE()  
GROUP BY MONTH([join_date]);

I need to form the stored procedure ( I will pass month and year as parameters) and I got output as January, December not as Dec 22 under the month column, From the month value and year, I need to generate the last 24 months count result.

Answer 1

The problem with your original query is two fold. First, your GROUP BY clause only included the month. Second, your DATEADD in the WHERE wasn't used correctly. Here's a minimally modified correction to your code:

Updated to sort by year then month numerically as per comments.

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE EMP_TABLE (
  join_date datetime
  );

INSERT INTO EMP_TABLE (join_date)
VALUES 
  ('2022-12-1')
  , ('2022-12-2')
  , ('2022-12-3')
  , ('2022-11-4')
  , ('2022-11-5')
  , ('2021-12-6')
  , ('2021-11-6')
  , ('2021-11-8')
  , ('2021-11-9')
  , ('2021-4-6')
;

Query 1:

WITH prelim as (
  SELECT 
    YEAR([join_date]) AS Year
    , MONTH([join_date]) AS MONTH
    , COUNT(1) AS "Total.EMP"
  FROM [EMP_TABLE] 
  WHERE [join_date] BETWEEN DATEADD(YEAR, -2, GETDATE()) AND GETDATE()  
  GROUP BY YEAR([join_date]), MONTH([join_date])
)
SELECT 
  [Year]
  , [Month]
  , DATENAME(MM,DATEFROMPARTS([Year],[Month],1)) AS MonthName
  , [Total.EMP]
FROM prelim
ORDER BY [Year] DESC, [Month] DESC

Results:

| Year | Month | MonthName | Total.EMP |
|------|-------|-----------|-----------|
| 2022 |    12 |  December |         3 |
| 2022 |    11 |  November |         2 |
| 2021 |    12 |  December |         1 |
| 2021 |    11 |  November |         3 |
| 2021 |     4 |     April |         1 |