问题描述:

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 :)

UPDATE DATE_DIMENSION_001

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

END

END

网友答案:

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