How to get prior data in a missing dates in sql table

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.

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:

• Generate a set or sequence without loops - 1 - Aaron Bertrand

• Generate a set or sequence without loops - 2 - Aaron Bertrand

• Generate a set or sequence without loops - 3 - Aaron Bertrand

• The "Numbers" or "Tally" Table: What it is and how it replaces a loop - Jeff Moden

• Creating a Date Table/Dimension in sql Server 2008 - David Stein

• Calendar Tables - Why You Need One - David Stein

• Creating a date dimension or calendar table in sql Server - Aaron Bertrand

• tsql Function to Determine Holidays in sql Server - Aaron Bertrand

• F_table_date - Michael Valentine Jones

StackExchange Database Administrators Q#167834, answer score: 3