I'm creating a Date_Dimension and have the Problem that i have to define "lastworkingdayofmonth" with Setting a flag on it.
I can handle all days but when the calculated they is a Holiday i cant get the day before to set the flag.
Please help me :)
SET ISLASTWORKINGDAYMONTH =
CASE WHEN ( CONVERT(VARCHAR(8), lastdayofmonth, 112) = Datekey ) AND IsWeekday = 1 AND IsHolidayAut = 0 THEN 1
WHEN ( CONVERT(VARCHAR(8), dbo.fn_LastWorkday(FullDate), 112) ) = Datekey AND IsHolidayAut = 0 THEN 1
WHEN ( CONVERT(VARCHAR(8), dbo.fn_LastWorkday(FullDate), 112) ) = Datekey AND IsHolidayAut = 1 THEN
CASE WHEN ( DATEADD(DD, -1, dbo.fn_LastWorkday(FullDate)) ) = CONVERT(DATE, Datekey) THEN 1
I think that something like this may work for you:
UPDATE dd SET ISLASTWORKINGDAYMONTH = 1 FROM DATE_DIMENSION_001 dd left join DATE_DIMENSION_001 dd_anti on DATEPART(year,dd.FullDate) = DATEPART(year,dd_anti.FullDate) and DATEPART(month,dd.FullDate) = DATEPART(month,dd_anti.FullDate) and dd_anti.FullDate > dd.FullDate and dd_anti.IsWeekday = 1 and dd_anti.IsHolidayAut = 0 WHERE dd.IsWeekday = 1 and dd.IsHolidayAut = 0 and dd_anti.FullDate is null
That is, we locate rows which are weekdays, not holidays, and for which (via
LEFT JOIN and the
null check in the
WHERE clause) we cannot locate another row for the same month, but a later date, and is also a weekday and not a holiday.