SQL Server query - Fill Missing Dates In a Date-Se

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 suggestion 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 |

Answer 2

I love wewesthemenace answer! Up-voted and have saved it for future reference. Here is another suggestion. Use this code in SQL command from Crystal. Left outer join to your table sales. Its long. Its tedious. But its easier to understand if you aren't an SQL expert which describes me pretty well :) If you figured out his answer and it works then disregard this solution.

Note: the from clause isn't serving any function except to satisfy the requirement of having a from clause in an SQL statement like this. If tblSales is a large table consider using one from your database which has less data and still left outer join to tblSales.

    select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','01') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','02') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','03') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','04') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','05') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','06') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','07') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','08') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','09') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','10') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','11') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','12') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','13') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','14') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','15') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','16') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','17') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','18') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','19') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','20') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','21') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','22') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','23') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','24') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','25') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','26') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','27') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','28') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','29') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','30') as DATE) as salesdate
from tblSales
UNION
select distinct
cast(concat(DATEPART(YYYY, GETDATE()),'-',DATEPART(MM,GETDATE()),'-','31') as DATE) as salesdate
from tblSales