CODEWITHSUNDEEP
sqlsql-server
mHelpMe
mHelpMe

Reputation: 6668

Adjusting date if date falls on a weekend or bank holiday

I have a query that inserts some new records into a table, myTbl from a temporary table, myTblTemp.

In the query I adjust one of the fields 'AnnDate' if the date is a Saturday or Sunday. However I also need to adjust the AnnDate if the date is a bank holiday.

I have a table, tblBankHoliday which lists all required up coming bank holidays. How do I adjust my query (or do I need a seperate one) that will add one day to the date if the AnnDate is found in the tblBankHoliday?

My current query

insert into myTbl(AnnDate, USDVal)
case DATENAME(WEEKDAY, AnnDate) 
       when 'Saturday' 
       then DATEADD(DD, 2, AnnDate) 
       when 'Sunday' 
       then DATEADD(DD, 1, AnnDate) 
       else AnnDate 
end
from myTblTemp

Upvotes: 0

Views: 495

Answers (2)

EzLo
EzLo

Reputation: 14189

Sticky bit's answer is way better for your case.

You can use a recursive CTE to cycle through non-valid dates and keep increasing by 1 day until your validation fulfills.

;WITH PatchedDates AS
(
    SELECT
        InitialAnnDate = C.AnnDate,
        CorrectedAnnDate = C.AnnDate,
        USDVal = C.USDVal,
        IsDateOK = CASE 
            WHEN 
                EXISTS (SELECT 1 FROM tblBankHoliday AS X WHERE X.BankHoliday = C.AnnDate) OR 
                DATENAME(WEEKDAY, C.AnnDate) IN ('Saturday', 'Sunday') THEN 'No' 
            ELSE 'Yes' END
    from 
        myTblTemp AS C
        LEFT JOIN tblBankHoliday AS T ON C.AnnDate = T.BankHoliday

    UNION ALL

    SELECT
        InitialAnnDate = C.InitialAnnDate,
        CorrectedAnnDate = DATEADD(DAY, 1, C.CorrectedAnnDate),
        USDVal = C.USDVal,
        IsDateOK = CASE 
            WHEN 
                EXISTS (SELECT 1 FROM tblBankHoliday AS X WHERE X.BankHoliday = DATEADD(DAY, 1, C.CorrectedAnnDate)) OR 
                DATENAME(WEEKDAY, DATEADD(DAY, 1, C.CorrectedAnnDate)) IN ('Saturday', 'Sunday') THEN 'No' 
            ELSE 'Yes' END
    FROM
        PatchedDates AS C
    WHERE
        C.IsDateOK = 'No'
)
insert into myTbl(
    AnnDate, 
    USDVal)
SELECT
    AnnDate = C.CorrectedAnnDate,
    USDVal = C.USDVal
FROM
    PatchedDates AS C
WHERE
    C.IsDateOK = 'Yes'

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

Make a table e.g. thedays. Insert all days from the relevant period in it. e.g. in a column theday. In another column e.g. isworkday mark workdays with 1 non workdays with 0.

Then you can select the next workday at or after a given day in you query.

insert into myTbl(AnnDate, USDVal)
(SELECT min(theday)
        FROM thedays
        WHERE theday >= AnnDate
              AND theday.isworkday = 1)
...
from myTblTemp

Upvotes: 1

Related Questions