Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 143
  • Last Modified:

SQL Query Get Data from Previous Date

I am looking to get previous data where the date's corresponding data is null.  Basically I want to pre-fill the rate from previous business days for the weekend and holidays where it did not exist in the rate table; therefore I joined a calendar table to ensure it returns a date value.

In the example shown below for 'gb' and 'eu' rates, I want to fill in for the weekends from that previous Friday's data.  Is this possible without a stored procedure?


SQL QUERY
select distinct a.DateKey as CalendarDate
, fx.NumeratorCurrCode as ISO_Curr
, fx.SpotRate
, fx.Mod_SpotRate
from AdvDateList a
left outer join (select NumeratorCurrCode, DenominatorCurrCode
				, AsOfDate, SpotRate, 1/SpotRate as Mod_SpotRate -- Mod_SpotRate
				from APX.FxRateHistory fx
				where NumeratorCurrCode in ('gb','eu')
				and DenominatorCurrCode = 'us'
				) fx on a.DateKey = fx.AsOfDate
where a.DateKey between (getdate()-6) and (getdate()-1) -- returns previous 5 days

Open in new window



ORIGINAL RESULTS                  
Date      ISO_Curr      SpotRate      Mod_SpotRate
9/17/2014      eu      0.7773      1.286504567
9/17/2014      gb      0.6144      1.627604167
9/18/2014      eu      0.7739      1.292156609
9/18/2014      gb      0.6101      1.639075561
9/19/2014      eu      0.7794      1.283038235
9/19/2014      gb      0.614      1.628664495
9/20/2014      NULL      NULL      NULL
9/21/2014      NULL      NULL      NULL

                  
                  
EXPECTED RESULTS                  
Date      ISO_Curr      SpotRate      Mod_SpotRate
9/17/2014      eu      0.7773      1.286504567
9/17/2014      gb      0.6144      1.627604167
9/18/2014      eu      0.7739      1.292156609
9/18/2014      gb      0.6101      1.639075561
9/19/2014      eu      0.7794      1.283038235
9/19/2014      gb      0.614      1.628664495
9/20/2014      eu      0.7794      1.283038235
9/20/2014      gb      0.614      1.628664495
9/21/2014      eu      0.7794      1.283038235
9/21/2014      gb      0.614      1.628664495
0
KANEDA 0149
Asked:
KANEDA 0149
  • 3
1 Solution
 
PortletPaulCommented:
Is this SQL Server? (or what dbms is this for?) and what version? (this is important as solution options can change by version)

& it's a good idea to include this info in any SQL related question
0
 
KANEDA 0149Author Commented:
Hi Paul, my apologizes.  It is SSMS 2008.
0
 
KANEDA 0149Author Commented:
SSMS 2008 R2 actually
0
 
SharathData EngineerCommented:
try this.
;WITH cte 
     AS (SELECT DISTINCT a.DateKey            AS CalendarDate, 
                         fx.NumeratorCurrCode AS ISO_Curr, 
                         fx.SpotRate, 
                         fx.Mod_SpotRate 
           FROM AdvDateList a 
                LEFT OUTER JOIN (SELECT NumeratorCurrCode, 
                                        DenominatorCurrCode, 
                                        AsOfDate, 
                                        SpotRate, 
                                        1 / SpotRate AS Mod_SpotRate 
                                   FROM APX.FxRateHistory fx 
                                  WHERE NumeratorCurrCode IN ( 'gb', 'eu' ) 
                                    AND DenominatorCurrCode = 'us') fx 
                             ON a.DateKey = fx.AsOfDate 
          WHERE a.DateKey BETWEEN ( Getdate() - 6 ) AND ( Getdate() - 1 )) 
SELECT t1.CalendarDate, 
       COALESCE(t1.ISO_Curr, t2.ISO_Curr)         ISO_Curr, 
       COALESCE(t1.SpotRate, t2.SpotRate)         SpotRate, 
       COALESCE(t1.Mod_SpotRate, t2.Mod_SpotRate) Mod_SpotRate 
  FROM cte t1 
       LEFT JOIN cte t2 
              ON t2.CalendarDate = Dateadd(dd, CASE Datepart(dw, t1.CalendarDate) 
                                                 WHEN 7 THEN -1 
                                                 WHEN 1 THEN -2 
                                                 ELSE 0 
                                               END, t1.CalendarDate) 
                 AND t2.ISO_Curr = COALESCE(t1.ISO_Curr, t2.ISO_Curr); 

Open in new window


here is an example:
http://sqlfiddle.com/#!3/618128/14
0
 
KANEDA 0149Author Commented:
Thank you Sharath, that worked exactly as I needed.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now