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 store 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.

CodePudding user response：

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:

CREATE TABLE EMP_TABLE (
  join_date datetime
  );

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

SELECT 
  DATENAME(MM,[join_date]) AS Month
  , YEAR([join_date]) AS Year
  , COUNT(1) AS "Total.EMP"
FROM [EMP_TABLE] 
WHERE [join_date] BETWEEN DATEADD(YEAR, -2, GETDATE()) AND GETDATE()  
GROUP BY YEAR([join_date]), DATENAME(MM,[join_date]);

fiddle