获取每个月末的日期列表?

Question

我需要使用表1和表2从当前日期开始获得过去3年的每个月的结束，不包括法定假日和周末。表1列出了2025-2017年的所有日期。表2列出了2025-2017年的所有法定假日。

如何创建SQL脚本来达到这个结果?任何建议都有帮助。预期结果将是月底最近3年的日期列表，不包括法定假日和周末。

表1有2列，DateId和FullDate列

DateID Fulldate
1010392 2019-12-1
1010393 2019-12-2
1010394 2019-12-3
1010395 2019-12-4
.
.
101086 2019-12-31

表2有2列，DateId和法定假日

Date ID   Stat_Holidays
101085     2019-12-25
101086     2019-12-26

返回的结果应该看起来像

WeekDay_FullDate_Past3yrs
2019-12-31
2020-1-31
2020-2-28
2020-3-31

尝试如下:

select * from
( select a.Date from Table1 a where a.Date <= 
'20221215' and a.Date >= DATEADD (YEAR, -3, getdate()) ) as t1
join
( select EOMONTH(a.Date) as Date from Table1 a where a.Date <= '20221215' and a.Date >= DATEADD (YEAR, -3, getdate()) ) as t2 on t1.Date = t2.Date 

尝试了下面链接的解决方案，它不能解决我的问题。我希望得到一个月的最后一个工作日的列表(不包括周末和假期)为过去3年
SQL Server -获取最后的业务数据不包括假期和周末

Answer 1

您可以按月份和年份分组，并取最大日期(不包括节假日和周末):

SET DATEFIRST 1;
DECLARE @CurrentDate DATE = '20221215';
WITH cte
AS
(
SELECT MAX(Date ) as EOMDate
FROM Table1 
WHERE DATEPART(weekday,Date) NOT IN (6,7)
AND Date NOT IN (SELECT Date FROM Table2)
GROUP BY YEAR(Date),MONTH(Date)
)
SELECT *
FROM cte
WHERE cte.EOMDate BETWEEN DATEADD(YEAR,-3,@CurrentDate) AND @CurrentDate;

Answer 2

这应该可以工作，并在您的主表中给出每个月的最后一个工作日。只需按所需时间段进行过滤:

SELECT TOP 1 WITH TIES FullDate
FROM Table1
WHERE FullDate NOT IN (SELECT Stat_Holidays FROM Table2) -- not holiday
AND DATEPART(weekday, FullDate) NOT IN (7, 1) -- not saturday and sunday
ORDER BY DENSE_RANK() OVER(PARTITION BY YEAR(FullDate), MONTH(FullDate) ORDER BY FullDate DESC)

Answer 3

用您的表名和列名检查这个

select  year(dates) _Year ,month(dates) _Month,EOMONTH(dates) endofMOnth from tabledate1 where DATENAME(DW, dates) not in ('Saturday','Sunday') 
and EOMONTH(dates)  not in (select holidaydate from tableholidays)
Group by year(dates),month(dates),EOMONTH(dates) 
order by year(dates) ,month(dates)