I'm trying to add days for following up when a holiday falls during one of the scheduled follow up days. In this case July 1 was the charge date and should be followed up on no later than July 5, but in this case July 4 falls during the follow up window so I need to adjust the follow up date. Rule: When charge date is Friday and Monday is a holiday I need to add a day to the follow up timeframe.

SELECT
    c.*,
    CASE
        WHEN Date BETWEEN CONVERT(Date,c.chargeDateTime) AND c.[FollowUp] AND c.chargeDay='Friday' AND d.HolidayFlag ='Y' THEN DATEADD(DD,5,CONVERT(Date,c.chargeDateTime))  
    ELSE c.[FollowUp]
    END AS 'FollowUpAdjusted'
FROM Sales.Purchases AS c
LEFT JOIN Dim.Date AS d ON CONVERT(Date, c.chargeDateTime) = d.Date

CodePudding user response：

I think your problem is that you're joining the date table on the charge date, but really you need to be joining on the Monday which you want to check is a holiday.

That said, your rules seem very weird. It's only when it's charged on a Friday and the Monday is a holiday that you get an extra day? What if it's Thursday and Monday was a holiday? Or if the holiday was on a Tuesday? What if there are 2 holidays?

If what you want to do is make it 5 days, but add a day for every holiday or weekend within the range you could do this:

SELECT
    Purchases.*,
    DATEADD(
        DAY, 
        COALESCE(
            (   SELECT  COUNT(1)
                FROM    Dim.Date
                WHERE   Dim.Date BETWEEN CONVERT(Date,Purchases.chargeDateTime) AND DATEADD(DAY,5,Purchases.chargeDateTime)
                AND     (       Dim.Date.HolidayFlag = 'Y'
                            OR  DATEPART(WEEKDAY,Dim.Date) IN (1,7)
                        )
            ),
            0
        )   5,
        Purchases.chargeDateTime
    ) [FollowUp]
FROM Sales.Purchases

Just remove the OR DATEPART line if you don't want to give an extra day for spanning a weekend.