3

How to get prior data from a sql table?

In a table we have data as following and don't have data on holidays and weekends.

rate column         ratevalue

12/29/16              1.2266 (Thursday)
12/30/16              1.2345 (Friday)

01/03/17              1.2240 (Tuesday)

My requirement is to get data based on prior day as below

12/29/16              1.2266 (Thursday)
12/30/16              1.2345 (Friday)
12/31/16              1.2345 (Saturday)
01/01/17              1.2345 (Sunday)
01/02/17              1.2345 (Monday, New Year Holiday)
01/03/17              1.2240 (Tuesday)

Similarly the logic applies to any holidays where the rate doesn't get picked up. It should show the previous day's value.

  • What version of Sql Server are you using? Sql2008? Sql2012? – Scott Hodgin Mar 22 '17 at 9:35
  • Where do you get the missing dates from? – mustaccio Mar 22 '17 at 13:45
3

You should really have a Calendar table for this sort of thing. Using a recursive cte is going to be one of the worst performing options as the size of your date range gets larger.

For only 152kb in memory, you can have 30 years of dates in a table with this:

/* dates table */
declare @fromdate date = '20000101';
declare @years    int  = 30;
/* 30 years, 19 used data pages ~152kb in memory, ~264kb on disk */
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate)))
    [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
into dbo.Dates
from n as deka cross join n as hecto cross join n as kilo
               cross join n as tenK cross join n as hundredK
order by [Date];
create unique clustered index ix_dbo_Dates_date
  on dbo.Dates([Date]);

Without taking the actual step of creating a table, you can use it inside a common table expression with just this:

declare @fromdate date = '20161229'; 
declare @thrudate date = '20170103';
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, @thrudate)+1) 
      [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo
                cross join n as tenK cross join n as hundredK
   order by [Date]
)
select [Date]
from dates;

Use either with outer apply() like so:

select 
    d.[Date]
  , r.rateValue
from dates d
  outer apply (
    select top 1 rateValue 
    from rates i
    where i.rateDate <= d.Date
    order by i.rateDate desc
    ) r;

rextester demo: http://rextester.com/NVY8460

returns:

+------------+-----------+
|    Date    | rateValue |
+------------+-----------+
| 2016-12-29 | 1.2266    |
| 2016-12-30 | 1.2345    |
| 2016-12-31 | 1.2345    |
| 2017-01-01 | 1.2345    |
| 2017-01-02 | 1.2345    |
| 2017-01-03 | 1.2240    |
+------------+-----------+

Number and Calendar table reference:

1

In this case you need to generate a series of dates.

I'd suggest to use a RECURSIVE query.

DECLARE @tbl TABLE(rate_column datetime, rate_value decimal(18,4));
INSERT INTO @tbl VALUES
('20161229', 1.2266),
('20161230', 1.2345),
('20170103', 1.2240),
('20170104', 1.2332),
('20170107', 1.2222);

Select first date of your table:

    SELECT   TOP (1) rate_column, rate_value
    FROM     @tbl
    WHERE    rate_column = '20161229'

Then add day by day until rate_column reach last date.

Use a subquery to get rate_value for the current date, or in case its value IS NULL, use last rate_value.

WITH Dates AS 
(
    SELECT   TOP (1) rate_column, rate_value
    FROM     @tbl
    WHERE    rate_column = '20161229'
   UNION ALL 
    SELECT    DATEADD(DAY, 1, d.rate_column) as rate_column,
              COALESCE((SELECT t.rate_value
                        FROM   @tbl t
                        WHERE  t.rate_column = DATEADD(DAY, 1, d.rate_column)
                       ), d.rate_value) rate_value
    FROM      Dates d
    WHERE     DATEADD(DAY, 1, d.rate_column) <= '20170107'
)
SELECT rate_column, rate_value
FROM   Dates
OPTION (MAXRECURSION 0);

This is the final result:

rate_column         | rate_value
:------------------ | :---------
29/12/2016 00:00:00 | 1.2266    
30/12/2016 00:00:00 | 1.2345    
31/12/2016 00:00:00 | 1.2345    
01/01/2017 00:00:00 | 1.2345    
02/01/2017 00:00:00 | 1.2345    
03/01/2017 00:00:00 | 1.2240    
04/01/2017 00:00:00 | 1.2332    
05/01/2017 00:00:00 | 1.2332    
06/01/2017 00:00:00 | 1.2332    
07/01/2017 00:00:00 | 1.2222    

dbfiddle here

0

Something worth pointing out. If you have a report or something that uses a start date and an end date you could use those to quickly generate your days and then join to that result instead of a date table. This is based on Zim's solution but with a different way of getting your dates.

DECLARE 
     @StartDate date = '12/29/16'
    ,@EndDate date = '01/03/17';

WITH rCTE AS (
    SELECT @StartDate AS RateDate
    UNION ALL 
    SELECT DATEADD(DAY, 1, RateDate) FROM rCTE
    WHERE RateDate <= @EndDate )

SELECT 
     r.RateDate
    ,RateValue
FROM rCTE r
CROSS APPLY (
    SELECT TOP 1 RateValue
    FROM #Test t
    WHERE t.RateDate <= r.RateDate
    ORDER BY t.RateDate DESC ) AS z
  • Note that, by using CROSS APPLY instead of OUTER APPLY, you will not have a row for any date where the CROSS APPLY expression doesn't return a row. Probably has no impact in this specific example, but I'd recommend using OUTER APPLY in most cases, just to be safe. – RDFozz Nov 8 '17 at 21:58
  • I'll also note that, while this is nothing truly new (both previous answers provided options using hard-coded dates, regardless of the source; this is really just merging parts of the previous two answers), some may find it a bit easier to follow than either previous answer. – RDFozz Nov 8 '17 at 22:03
  • Neither of the two solutions consider a parameter nor the way we use that to build a contiguous date range with the recursive CTE. If this is a report then it is likely in SSRS and you would pass in a date range of start and end to see values in a period in the report. This is taking that in to consideration and simplifying the solution without the need for a date table. Also, CROSS APPLY will always work for this given scenario here so why would I use OUTER APPLY? That wouldn't make sense to do so. – user7593937 Nov 9 '17 at 14:22
  • Yes, CROSS APPLY works fine for this given scenario - where a value is available as long as either there is a value for the date, or there was a value for some previous date. And, in this case, if the start of the date range came before the first available value, then leaving out the previous dates would probably make sense. However, in "missing date" situations, it's not uncommon to want to show the missing dates with a default value, where the expression in the APPLY may not return a row. Hence, why the other solutions used OUTER APPLY. Worth noting for future readers. – RDFozz Nov 9 '17 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.