SQLTeam.com | Weblogs | Forums

How to add the missing dates in sql?

tsql

#1

Table tblHistory missing rows from these dates on my example. Therefore they are not included in the results of my sql. I want to add these dates in my sql and use same t_stock value from previous date.
My query:

SELECT 
    t_material, 
    t_stock, 
    t_price, 
    date(t_date) as t_date 
FROM
    tblHistory
WHERE 
    t_material = 'M3451' AND
    t_date between '2016-01-20' AND '2016-01-31 23:59:59'
ORDER BY 
    t_material,
    t_date;

Return:

t_material------t_stock------t_price-----t_date
M3451-----------343----------175---------2016-01-20
M3451-----------330----------175---------2016-01-21
M3451-----------360----------175---------2016-01-23
M3451-----------315----------175---------2016-01-24
M3451-----------420----------175---------2016-01-27
M3451-----------430----------175---------2016-01-28
M3451-----------500----------175---------2016-01-29
M3451-----------305----------175---------2016-01-30
M3451-----------307----------175---------2016-01-31

But I want it to return like this:

t_material------t_stock------t_price-----t_date
M3451-----------343----------175---------2016-01-20
M3451-----------330----------175---------2016-01-21
M3451-----------330----------175---------2016-01-22 <<<---Add missing date and use same t_stock from previous date.
M3451-----------360----------175---------2016-01-23
M3451-----------315----------175---------2016-01-24
M3451-----------315----------175---------2016-01-25 <<<---Add missing date and use same t_stock from previous date.
M3451-----------315----------175---------2016-01-26 <<<---Add missing date and use same t_stock from previous date.
M3451-----------420----------175---------2016-01-27
M3451-----------430----------175---------2016-01-28
M3451-----------500----------175---------2016-01-29
M3451-----------305----------175---------2016-01-30
M3451-----------307----------175---------2016-01-31

#2

This is one way of accomplishing your task:

declare @startdate    datetime=cast('2016-01-20' as datetime);
declare @enddate      datetime=cast('2016-02-01' as datetime);
declare @earliestdate datetime=cast('2015-01-01' as datetime);

with tally(startdate,enddate) /* Generate 1000 dates */
  as (select top (datediff(day,@startdate,@enddate))
             dateadd(day,row_number() over(order by (select null))-1,@startdate)
            ,dateadd(day,row_number() over(order by (select null)),@startdate)
        from (      values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t1(n)
        cross join (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t2(n)
        cross join (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t3(n)
     )

    ,cte(t_material,t_stock,t_price,t_date,rn)
  as (select b.t_material
            ,b.t_stock
            ,b.t_price
            ,cast(a.startdate as date)as t_date
            ,row_number() over(partition by b.t_material
                                           ,a.startdate
                               order by b.t_date desc
                              )
             as rn
        from tally as a
             inner join tblhistory as b
                     on b.t_date<a.enddate
                    and b.t_date>=@earliestdate
    )
select t_material
      ,t_stock
      ,t_price
      ,t_date
  from cte
 where rn=1
;