SQL replace date values if date is a holiday or weekend

Question

this is referring to my previous post: Link

System: Sql Server 2012

With this script I take the Friday values if the day of CutoffDate is Saturday or Sunday.

SELECT DISTINCT dbo.ECB.Currency + '-' + CAST(dbo.myTable.CutoffDate AS varchar(30)) AS ComboCurrDate, dbo.ECB.Rate AS ECBrate
FROM         dbo.ECB INNER JOIN
                      dbo.myTable ON dbo.ECB.Date = CASE WHEN datepart(weekday, dbo.myTable.CutoffDate) IN (6, 7) THEN dateadd(d, - ((datepart(weekday, 
                      dbo.myTable.CutoffDate) + 1 + @@DATEFIRST) % 7), dbo.myTable.CutoffDate) ELSE dbo.myTable.CutoffDate END AND 
                      dbo.ECB.Currency = dbo.myTable.InvoiceCurrency
WHERE     (dbo.myTable.InvoiceCurrency NOT LIKE 'EUR%')

But now I need to rewrite this to also deal with holidays. Or better, deal with non-existing records in the ECB-table.

For example: For Friday 29th March there is no record in the ECB-table but the myTable.CutoffDate is set to this date. With the above script I don't get values for this day.

How could I now assign the ECB-value of the 28th to the CutoffDate?

In general: If there is no record for a day in the ECB-table then try the previous date within the ECB-table and so on.

Hope I could explain this clearly.

Thank you for your help.

Gordon Linoff · Accepted Answer · 2013-04-04T10:36:45.977

1

I think you should get the most recent ECB date for each cutoff date in your data.

The following does this as a correlated subquery:

SELECT DISTINCT ECB.Currency + '-' + CAST(m.CutoffDate AS varchar(30)) AS ComboCurrDate,
                ECB.Rate AS ECBrate
FROM (select m.*,
             (select     top     1     ecb.date
              from ecb
               where ecb.currency = m.InvoiceCurrency and ecb.date <= m.CutoffDate
               order by ecb.date desc
             ) as ECBDateToUse
      from mytable
     ) m left outer join
     ECB 
     ON ECB.Date = m.ECBDateToUse and
        ECB.Currency = m.InvoiceCurrency
WHERE (m.InvoiceCurrency NOT LIKE 'EUR%')

edited Apr 04 '13 at 10:36

answered Apr 03 '13 at 14:35

Gordon Linoff

1,242,037
58
646
786

Gordon: I had to remove the INNER JOIN stmt to get it to work. "Wrong syntax". Data looks good so far. Can you please confirm whether the inner join is needed or not? – SvenB Apr 04 '13 at 06:27
Well it seems to work without the Inner Join stmt, so I will mark this as the right answer. Thank you. – SvenB Apr 04 '13 at 10:37

Matt Busche · Answer 2 · 2013-04-03T14:41:02.997

I think what you're looking for a LEFT OUTER JOIN

SELECT DISTINCT dbo.ECB.Currency + '-' + CAST(dbo.myTable.CutoffDate AS varchar(30)) AS ComboCurrDate, dbo.ECB.Rate AS ECBrate
FROM mytable LEFT OUTER JOIN ECB ON dbo.ECB.Date = CASE WHEN datepart(weekday, dbo.myTable.CutoffDate) IN (6, 7) THEN dateadd(d, - ((datepart(weekday, 
                  dbo.myTable.CutoffDate) + 1 + @@DATEFIRST) % 7), dbo.myTable.CutoffDate) ELSE dbo.myTable.CutoffDate END AND 
                  dbo.ECB.Currency = dbo.myTable.InvoiceCurrency

This will return you all records from mytable regardless if there is a result in the ECB table. You can add a filter of AND ECB.date IS NULL to only pull in records that don't exist in the ECB table

. . A million apologies. I had trouble uploading my answer and I ended up overwriting yours (to fix my problem, I have to keep re-editing and re-editing my answer and I misclicked on yours instead). Arrgg! — Gordon Linoff, Apr 03 '13 at 14:39

score 0 · Answer 3 · answered Apr 03 '13 at 14:39

Use an outer join, and put all predicate conditions affecting the outer side of the join in join condition, not in where clause. Also, may I humbly suggest you use table aliases and format to make sql more readable. You will get answers to your questions faster if readers can understand your Query with less effort.

SELECT DISTINCT e.Currency + '-' + 
    CAST(m.CutoffDate AS varchar(30)) ComboCurrDate, 
    e.Rate ECBrate
FROM dbo.ECB e  Left JOIN dbo.myTable m 
     ON e.Currency = m.InvoiceCurrency
          And m.InvoiceCurrency NOT LIKE 'EUR%'
          And e.Date = 
               CASE WHEN datepart(weekday, m.CutoffDate) IN (6, 7) 
                    THEN dateadd(d, - ((datepart(weekday, 
                         m.CutoffDate) + 1 + @@DATEFIRST) % 7), m.CutoffDate) 
                    ELSE m.CutoffDate END

SQL replace date values if date is a holiday or weekend

3 Answers3