SQL View syntax question

Hello all,

I have a view that has records such as this:

SELECT  ExchangeRateDate, CurrencyCode, ExchangeRate FROM ExchangeRate
ExchangeRateDate    CurrencyCode     ExchangeRate
2/19/2015                    GBP                      1.1255
2/19/2015                    ANG                      0.6522
2/20/2015                    GBP                      1.3525
2/20/2015                    ANG                      0.7525
2/23/2015                    GBP                      1.8525
2/23/2015                    ANG                      0.4525

Problem is the source table does not store weekend exchange rates.   What I want to do is carry forward any Friday record to the Saturday and Sunday so creating records for this where in the above example I would have 4 more records.   How can I accomplish this in my view?
sbornstein2Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
SELECT DATEADD(DAY, day_adjustment, er.ExchangeRateDate) AS ExchangeRateDate, er.CurrencyCode, er.ExchangeRate
FROM ExchangeRate er
OUTER APPLY (
    SELECT 0 AS day_adjustment, 'Fri' AS day_base
    UNION ALL
    SELECT *
    FROM (
        SELECT 1 AS day_adjustment, 'Sat' AS day_base UNION ALL
        SELECT 2, 'Sun'
    ) AS derived
    WHERE DATEDIFF(DAY, 0, er.ExchangeRateDate) % 7 = 4 --Fri
) AS oa1
0
 
sbornstein2Author Commented:
awesome thanks Scott
0
 
sbornstein2Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.