Get list of dates that falls in End Of each Month?-CodePudding

I need to get end of each month for the past 3 yrs from the current date excluding statutory holidays and weekends using table1 and table2. Table1 has all the dates ranging from year 2025-2017. Table2 has all the statutory holidays for the years ranging from 2025-2017.

How to create SQL script for to attain this result? Any suggestions could help. Expected result would be list of date last 3yrs of endofmonth excluding statutory holidays and weekends.

Table 1 has 2 columns, DateId and FullDate column

 DateID Fulldate

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

Table 2 has 2 columns, DateId and Statutory_Holidays

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

And the returned results should look like

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

Tried the below:

 select * from
 ( select a.FullDate from ref.date a where a.fulldate <= 
  '20221215' and a.fulldate >= DATEADD (YEAR, -3, getdate()) ) as t1
 join
 ( select EOMONTH(a.fulldate) as Fulldate from ref.date a where a.fulldate <= '20221215' and a.fulldate >= DATEADD (YEAR, -3, getdate()) ) as t2 on t1.FullDate = t2.FullDate

tried the solution from the below link it dosen't solve my issue. I'm looking to get list of last workday of a month(excluding weekends and holiday) for the past 3yrs
SQL Server - Get Last Business Data excluding holidays and Weekends

CodePudding user response：

You can group by month and year and take the max date (excluding holidays and weekends):

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;

CodePudding user response：

This should work and give you the last working day for each month in your main table. Just filter by the desired time period:

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)

CodePudding user response：

Check this with your table name and column names.

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)