We help IT Professionals succeed at work.

SQL View syntax question

sbornstein2
sbornstein2 asked
on
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?
Comment
Watch Question

Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
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

Author

Commented:
awesome thanks Scott

Author

Commented:
thanks