SQL replace date values if date is Saturday or Sun

问题:

(Sorry, I couldn't think of a better title)

I have created the following select statement (SQL Server) to work as a view.

SELECT DISTINCT 
       dbo.ECB.Currency + '-' + 
           CAST(dbo.ECB.Date AS varchar(30)) AS ComboDate, 
       dbo.ECB.Rate AS ECBrate 
FROM 
    dbo.ECB 
INNER JOIN
    dbo.MyTable ON dbo.ECB.Date = dbo.MyTable.CutoffDate 
                AND dbo.ECB.Currency = dbo.MyTable.MyCurrency

My problem:

What I've noticed is that I have Saturday and Sunday dates in my CutoffDate column but not in the ECB date column - meaning I have no rates for those days.

Since I use the CutoffDate in Excel calculations I would like the view to "replace" the date values for Saturday and Sunday with Friday.

I found this working snippet and modified it to my needs.

SELECT 
    DISTINCT
    CASE WHEN DATEPART(weekday, dbo.CutoffDate) IN (6, 7) 
         THEN DATEADD(d, -((DATEPART(weekday, dbo.CutoffDate) + 1 + @@DATEFIRST) % 7), dbo.CutoffDate) 
         ELSE dbo.CutoffDate
    END
FROM
    MyTable

Now I need to find a way to combine both structures and this is where I'm hoping that you can help me out with.

I wanted to add the second select statement to my view but failed in doing so.

thank you.

回答1:

If you move your case statement into the join, it should work.

Change

dbo.MyTable ON dbo.ECB.Date = dbo.MyTable.CutoffDate

To

dbo.Mytable ON dbo.ECB.Date = case when datepart(weekday, dbo.CutoffDate) IN (6,7) 
    then dateadd(d, -((datepart(weekday, dbo.CutoffDate) + 1 + @@DATEFIRST) % 7), dbo.CutoffDate) 
    else dbo.CutoffDate 
end

Hope this helps.