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

Question

I have a table in database with numbers of tenants, each tenant lists a record of their sales per date. There are instance where in a tenant has NO SALES in particular date/s, therefore the date with no sales has NO RECORD in the table breaking a proper date sequence. Please see the sample table for illustration below:

I used this select query in SQL to display the output above

select tenant, date, sales
from tblSales
where date between '01/01/2015' and '01/05/2014'

What I need as a correct output: display complete date based on the selected date range on the where clause, when tenant has no record in a particular date, the query should add a record of date in that particular tenant and just add null value in the sales column like in this image:

1. as my initial solution, I thought of creating a temp table inserting a sequence of date based on the date range selected and use that to left join with the actual table.

Here's what I 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 into #DSRTdate (Date) values (DATEADD(day,@Counter,@dateFrom ))
    SET @Counter += 1
END
            
            

I used the above codes to get and insert in a temporary table the sequence data from the use selection, in the above case, it inserts 02/01/2015, 02/02/2015, 02/03/2015, 02/04/2015, AND 02/05/2015

select tenantcode, date, sales
into #DSRT2
from DAILYMOD 
where (date between @dateFrom and @dateTo)
   
select *
from #dsrtdate a
left join #DSRT2 b on a.date = b.date
order by b.tenantcode, a.date   

Then I used left join to display the missing dates but this results only to ONE TENANT only and it makes also the tenantname null. Like this:

Any suggestions would be highly appreciated.

Answer 1

You could do this using a Tally Table.

Basically, you use the Tally Table to generate sequence of dates from @startDate to @endDate and CROSS JOIN it to DISTINCT Item to generate all Date-Item combination. Then, the result will be LEFT-JOINed to tblSales to achieve the desired output.

SQL Fiddle

DECLARE
    @startDate  DATE = '20140101',
    @endDate    DATE = '20140105';

WITH E1(N) AS(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,E2(N) AS(SELECT 1 FROM E1 a, E1 b)
,E4(N) AS(SELECT 1 FROM E2 a, E2 b)
,Tally(N) AS(
    SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1) 
        ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
)
,CteAllDates(Item, dt) AS(
    SELECT x.Item, DATEADD(DAY, N - 1, @startDate)
    FROM Tally
    CROSS JOIN(
        SELECT DISTINCT Item 
        FROM tblSales
        WHERE [Date] BETWEEN @startDate AND @endDate
    ) AS x
)
SELECT d.*, ts.Sales
FROM CteAllDates d
LEFT JOIN tblSales ts
    ON ts.Item = d.Item
    AND ts.Date = d.dt
WHERE
    ts.[Date] BETWEEN @startDate AND @endDate
ORDER BY d.Item, d.dt

---

Here is an alternative. Instead of the cascading CTEs, use sys.columns to generate the Tally Table.:

DECLARE
    @startDate  DATE = '20140101',
    @endDate    DATE = '20140105';

WITH Tally(N) AS(
    SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1) 
        ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM sys.columns a, sys.columns b
)
,CteAllDates(Item, dt) AS(
    SELECT x.Item, DATEADD(DAY, N - 1, @startDate)
    FROM Tally
    CROSS JOIN(
        SELECT DISTINCT Item 
        FROM tblSales
        WHERE [Date] BETWEEN @startDate AND @endDate
    ) AS x
)
SELECT d.*, ts.Sales
FROM CteAllDates d
LEFT JOIN tblSales ts
    ON ts.Item = d.Item
    AND ts.Date = d.dt
WHERE
    ts.[Date] BETWEEN @startDate AND @endDate
ORDER BY 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 |