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.

``UPDATE DATE_DIMENSION_001SET ISLASTWORKINGDAYMONTH =CASE WHEN ( CONVERT(VARCHAR(8), lastdayofmonth, 112) = Datekey ) AND IsWeekday = 1 AND IsHolidayAut = 0 THEN 1WHEN ( CONVERT(VARCHAR(8), dbo.fn_LastWorkday(FullDate), 112) ) = Datekey AND IsHolidayAut = 0 THEN 1WHEN ( CONVERT(VARCHAR(8), dbo.fn_LastWorkday(FullDate), 112) ) = Datekey AND IsHolidayAut = 1 THENCASE WHEN ( DATEADD(DD, -1, dbo.fn_LastWorkday(FullDate)) ) = CONVERT(DATE, Datekey) THEN 1ENDEND``

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 `dd_anti`, the `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.

Top