最近24个月的记录-按月计算需要从SQL server获取

Question

我有一个雇员表，我需要从指定的月份获取过去两年按月计算的雇员总数。

表设计

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

如果在下拉菜单中选择当前月份(dec 2022)，我需要显示下面的输出。

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

我尝试了以下查询，但计数添加了一月(2021和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]);

我需要形成存储过程(我将传递月份和年份作为参数)，我在月份列下得到的输出是January, December，而不是Dec 22，从月份值和年份，我需要生成最近24个月的计数结果。

Answer 1

您的原始查询的问题是双重的。首先，你的GROUP BY条款只包括月份。第二，你的DATEADD在WHERE中没有正确使用。以下是对代码的最小修改更正:

更新为根据注释按年和月进行数字排序。

SQL小提琴

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')
;

查询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

结果:

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