如何让T-SQL包含上个月的正确数据

Question

在下面的查询中，我返回了过去5个月的数据，不幸的是，我无法让它包括当月(2021年6月(的正确计数，它在0处返回了2，因为我有两个StartDate为14-06-2021和17-06-2021的条目，它们本应在该计数中。。我该怎么解决这个问题？。

我得到的输出(第6个月缺少2个(

year    month   EmployeeStartet
2021     2           8
2021     3           0
2021     4           0
2021     5           4
2021     6           0

我的查询

declare @thismonth1 as Date = DateAdd(
d, 
1 - DatePart(d, CURRENT_TIMESTAMP), 
CURRENT_TIMESTAMP
);
declare @lastMonth1 as Date = DateAdd(
d, 
1 - DatePart(d, CURRENT_TIMESTAMP), 
CURRENT_TIMESTAMP
);
declare @firstMonth1 as Date = DateAdd(m, -4, @lastMonth1);
WITH months AS (
SELECT 
@firstMonth1 AS thisMonth 
UNION ALL 
SELECT 
DateAdd(m, 1, thisMonth) AS thisMonth 
FROM 
months 
WHERE 
thisMonth < @lastMonth1
), 
data AS (
SELECT 
YEAR(StartDate) year, 
MONTH(StartDate) month, 
COUNT(StartDate) EmployeeStartet 
FROM 
EFP_EmploymentUser 
WHERE 
EmployType = 'fixed' 
AND StartDate BETWEEN @firstmonth1 
AND @thismonth1 
GROUP BY 
YEAR(StartDate), 
MONTH(StartDate)
) 
SELECT 
YEAR(m.thisMonth) AS year, 
MONTH(m.thisMonth) AS month, 
ISNULL(d.EmployeeStartet, 0) AS EmployeeStartet 
FROM 
months m 
LEFT OUTER JOIN data d ON d.year = YEAR(m.thisMonth) 
AND d.month = MONTH(m.thisMonth) 
ORDER BY 
m.thisMonth ASC;

Answer 1

尝试

declare @thismonth1 as Date = DateAdd(d, 1 - DatePart(d, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP); 
declare @lastMonth1 as Date = DateAdd(d, 1 - DatePart(d, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP); 
declare @firstMonth1 as Date = DateAdd(m, -4, @lastMonth1);
select @thismonth1, @lastMonth1, @firstMonth1;

这会给你

@thismonth1	@lastMonth1	@firstMonth1
2021-06-01	2021-02-01