Calculating Saturday and Sunday Business Date when Friday or Monday are a Holiday

Question

I am working on a calendar generator in MS SQL 2008 and need to generate the correct business day for each day in the month. Everything is working with this exception: If Friday or Monday are a Holiday, then I get an erroneous value for the Business Dates for that weekend (Saturday and Sunday). I already have a column that returns a value of 1 if a Monday (MHDY) is a Holiday, and a separate column for Friday as well (FHldy) and what I am attempting to do is assign a value of 1 in the MHDY and FHldy columns for Saturday and Sunday, if the Friday before or Monday after are holidays. Here is the portion of Code that produces the values mentioned above:

SELECT 
     MC.[Date]
    ,MC.[Year]
    ,MC.[Quarter]
    ,MC.[Month]
    ,MC.[Week]
    ,MC.[Day]
    ,MC.[DayOfYear]
    ,MC.[Weekday] -- This has first day of week as Monday
    ,MC.[KindOfDay]
    ,MC.[Description]
    ,CASE   WHEN Weekday BETWEEN 1 and 6 THEN Weekday +1
            WHEN Weekday = 7 THEN 1 END AS [DayofWeek]
    -- This will convert first day of week to Sunday
    , CASE WHEN MC.KindOfDay = 'Holiday' THEN 1 ELSE 0 END AS Holiday
    , CASE WHEN MC.KindOfDay <> 'BusDay' THEN 1 ELSE 0 END AS NonBDay
    , CASE WHEN MC.KindOfDay = 'BusDay' THEN 1 ELSE 0 END AS BDay
    , CASE WHEN MC.KindOfDay = 'Holiday' AND MC.Weekday = 1 THEN 1 ELSE 0 END AS MHDY
    , CASE WHEN MC.Date = DateAdd(Month, 1, MC.Date - Day(MC.Date) + 1) -1 THEN 1                  ELSE 0 END AS LDoM
    , CASE WHEN MC.KindOfDay = 'Holiday' AND MC.[Weekday] = 5 THEN 1 ELSE 0 END AS FHldy

INTO #AllC2


FROM #MasterCal MC

There is much more code to the Calendar than this portion, but this is where I am attempting to add this information.

Is there a way to modify the CASE Statement to say "If Monday is a Holiday, return a value of 1 for Saturday and Sunday to MHDY." The same would apply for Fridays.

So close... this is the last hurdle. I appreciate any help you can offer.

Answer 1

The solution to the problem turned out to be simple. I added lines to the CASE statement in the #MasterCal table to label Weekends before Monday holidays as Holidays in the KindOfDay field. the I added lines in the next section to tell it what date to assign those weekends that were listed as Holidays. Problem solved.