asp.net - SQL Server query - Fill Missing Dates In a Date-Sequenced in SQL using Tally Table -

i have table in database numbers of tenants, each tenant lists record of sales per date. there instance in tenant has no sales in particular date/s, therefore date no sales has no record in table breaking proper date sequence. please see sample table illustration below:

i used select query in sql display output above

select tenant, date, sales tblsales date between '01/01/2015' , '01/05/2014' 

what need correct output: display complete date based on selected date range on clause, when tenant has no record in particular date, query should add record of date in particular tenant , add null value in sales column in image:

1. as initial solution, thought of creating temp table inserting sequence of date based on date range selected , use left join actual table.

here's have started:

@datefrom datetime  = '02/01/2015', @dateto date = '02/05/2015'                   declare @maxnumdays int                 declare @counter int                  set @counter = 0                 set @maxnumdays = datediff(day, @datefrom  , @dateto) + 1                   create table #dsrtdate                     (                         date datetime                     )                      while @counter < @maxnumdays                     begin                         insert #dsrtdate (date) values (dateadd(day,@counter,@datefrom ))                         set @counter += 1                     end 

i used above codes , insert in temporary table sequence data use selection, in above case, inserts 02/01/2015, 02/02/2015, 02/03/2015, 02/04/2015, , 02/05/2015

 select   tenantcode, date, sales     #dsrt2     dailymod       (date  between  @datefrom  , @dateto )       select *     #dsrtdate  left join #dsrt2 b                     on a.date = b.date     order b.tenantcode, a.date    

then used left join display missing dates results 1 tenant , makes tenantname null. this:

any suggestion highly appreciated.

you using tally table.

basically, use tally table generate sequence of dates @startdate @enddate , cross join distinct item generate date-item combination. then, result left-joined tblsales achieve desired output.

sql fiddle

declare     @startdate  date = '20140101',     @enddate    date = '20140105';  e1(n) as(     select 1 union select 1 union select 1 union select 1 union select 1 union     select 1 union select 1 union select 1 union select 1 union select 1 ) ,e2(n) as(select 1 e1 a, e1 b) ,e4(n) as(select 1 e2 a, e2 b) ,tally(n) as(     select top (datediff(day, @startdate, @enddate) + 1)          row_number() over(order by(select null))     e4 ) ,ctealldates(item, dt) as(     select x.item, dateadd(day, n - 1, @startdate)     tally     cross join(         select distinct item          tblsales         [date] between @startdate , @enddate     ) x ) select d.*, ts.sales ctealldates d left join tblsales ts     on ts.item = d.item     , ts.date = d.dt     ts.[date] between @startdate , @enddate order d.item, d.dt 

---

here alternative. instead of cascading ctes, use sys.columns generate tally table.:

declare     @startdate  date = '20140101',     @enddate    date = '20140105';  tally(n) as(     select top (datediff(day, @startdate, @enddate) + 1)          row_number() over(order by(select null))     sys.columns a, sys.columns b ) ,ctealldates(item, dt) as(     select x.item, dateadd(day, n - 1, @startdate)     tally     cross join(         select distinct item          tblsales         [date] between @startdate , @enddate     ) x ) select d.*, ts.sales ctealldates d left join tblsales ts     on ts.item = d.item     , ts.date = d.dt     ts.[date] between @startdate , @enddate order d.item, d.dt 

---

result

|    item |         dt |  sales | |---------|------------|--------| | tenant1 | 2014-01-01 |    100 | | tenant1 | 2014-01-02 |    100 | | tenant1 | 2014-01-03 |    100 | | tenant1 | 2014-01-04 |   null | | tenant1 | 2014-01-05 |    100 | | tenant2 | 2014-01-01 |    100 | | tenant2 | 2014-01-02 |   null | | tenant2 | 2014-01-03 |   null | | tenant2 | 2014-01-04 |    100 | | tenant2 | 2014-01-05 |   null | | tenant3 | 2014-01-01 |    100 | | tenant3 | 2014-01-02 |   null | | tenant3 | 2014-01-03 |    100 | | tenant3 | 2014-01-04 |   null | | tenant3 | 2014-01-05 |    100 |