April 30, 2019 at 8:31 pm
I have a query I am running which needs to pull data from the last business day of every month, and the current work day of the current month(so we can get MTD numbers for a given month). I have something that works OK but I am hoping it can be improved upon and made automated so I don't need to update the dates when they change.
One issue I am having is when the last day of the month falls on a weekend or holiday, I need to pull the data from the prior business day. Currently I use the following query:
CAST(rd.Report_Date as Date) IN(EOMONTH(rd.Report_Date), '3/29/2019', '6/28/2019', '8/30/2019', '11/29/2019', CAST(GETDATE() as DATE)))
As you can see, the issue occurs when the EOMONTH date is not a working day(like March, June, August and November, and I have to go in and put the date in manually to include it.
I have a holiday table and I know that I can check against the day of the week number to determine if its a week day or not, but I am not sure how I would put something like this together for all 12 months. Any tips you could provide would be helpful.
To summarize, I want to:
April 30, 2019 at 11:12 pm
Use a Calendar table maybe?
May 1, 2019 at 7:21 am
Perhaps this code will get you started. It will extract the current month and calculate the last day of the previous month. In a CASE statement it will determine if this last day of previous month is a weekend-day and subtract days if required. This code doesn't take holidays in consideration, but you can expand this yourself by joining with your holiday table.
declare @CurrentMonth int = datepart(month, getdate())
set @CurrentMonth = 8-- overrule the variable value for testing purpose
select
CAST(GETDATE() as date) as CurrentDay
, mnth.PreviousMonthEnd
, DATEPART(weekday, mnth.PreviousMonthEnd) as 'WeekDay'-- 1=Sunday, 2=Monday, etc.)
, case
when DATEPART(weekday, mnth.PreviousMonthEnd) = 1
then dateadd(day, -2, mnth.PreviousMonthEnd)
when DATEPART(weekday, mnth.PreviousMonthEnd) = 7
then dateadd(day, -1, mnth.PreviousMonthEnd)
else mnth.PreviousMonthEnd
end as RequestedDate
from (select dateadd(year, datediff(year, 0, getdate()), 0) as YearStart
) as sub
cross apply (select dateadd(millisecond, -3, dateadd(month, @CurrentMonth -1, sub.YearStart)) as PreviousMonthEnd) mnth
;
May 1, 2019 at 2:08 pm
Perhaps this code will get you started. It will extract the current month and calculate the last day of the previous month. In a CASE statement it will determine if this last day of previous month is a weekend-day and subtract days if required. This code doesn't take holidays in consideration, but you can expand this yourself by joining with your holiday table.
declare @CurrentMonth int = datepart(month, getdate())
set @CurrentMonth = 8-- overrule the variable value for testing purpose
select
CAST(GETDATE() as date) as CurrentDay
, mnth.PreviousMonthEnd
, DATEPART(weekday, mnth.PreviousMonthEnd) as 'WeekDay'-- 1=Sunday, 2=Monday, etc.)
, case
when DATEPART(weekday, mnth.PreviousMonthEnd) = 1
then dateadd(day, -2, mnth.PreviousMonthEnd)
when DATEPART(weekday, mnth.PreviousMonthEnd) = 7
then dateadd(day, -1, mnth.PreviousMonthEnd)
else mnth.PreviousMonthEnd
end as RequestedDate
from (select dateadd(year, datediff(year, 0, getdate()), 0) as YearStart
) as sub
cross apply (select dateadd(millisecond, -3, dateadd(month, @CurrentMonth -1, sub.YearStart)) as PreviousMonthEnd) mnth
;
Very nice! So to run this for each month, I could basically make this a function and run it 12 times(once for each month) in the "IN" section of the report date?
May 1, 2019 at 2:22 pm
No need to run it multiple times. You can join it with a table that holds the months (or use some sort of a calendar table and extract the monthnumber instead).
declare @MonthTable table (MonthNumber int)
insert into @MonthTable
values (1)
, (2)
, (3)
, (4)
, (5)
, (6)
, (7)
, (8)
, (9)
, (10)
, (11)
, (12)
select
mnth.PreviousMonthEnd
, DATEPART(weekday, mnth.PreviousMonthEnd) as 'WeekDay'-- 1=Sunday, 2=Monday, etc.)
, case
when DATEPART(weekday, mnth.PreviousMonthEnd) = 1
then dateadd(day, -2, mnth.PreviousMonthEnd)
when DATEPART(weekday, mnth.PreviousMonthEnd) = 7
then dateadd(day, -1, mnth.PreviousMonthEnd)
else mnth.PreviousMonthEnd
end as RequestedDate
from @MonthTable MonthTable
cross apply (select dateadd(year, datediff(year, 0, getdate()), 0) as YearStart) yr
cross apply (select dateadd(millisecond, -3, dateadd(month, MonthNumber -1, yr.YearStart)) as PreviousMonthEnd) mnth
order by PreviousMonthEnd
;
This is a set-based approach in which you get your results in one go.
Edit:
Btw: the above code only applies to this year because of the GETDATE() in the "yr" cross apply. You could use a date value from a calendar table instead.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply