Skip to content Skip to sidebar Skip to footer

Fill Missing Dates In A Date-sequenced In Sql Using Tally Table

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, ther

Solution 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@startDateDATE='20140101',
    @endDateDATE='20140105';

WITH E1(N) AS(
    SELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1UNIONALLSELECT1
)
,E2(N) AS(SELECT1FROM E1 a, E1 b)
,E4(N) AS(SELECT1FROM E2 a, E2 b)
,Tally(N) AS(
    SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) +1) 
        ROW_NUMBER() OVER(ORDERBY(SELECTNULL))
    FROM E4
)
,CteAllDates(Item, dt) AS(
    SELECT x.Item, DATEADD(DAY, N -1, @startDate)
    FROM Tally
    CROSSJOIN(
        SELECTDISTINCT Item 
        FROM tblSales
        WHERE [Date] BETWEEN@startDateAND@endDate
    ) AS x
)
SELECT d.*, ts.Sales
FROM CteAllDates d
LEFTJOIN tblSales ts
    ON ts.Item = d.Item
    AND ts.Date = d.dt
WHERE
    ts.[Date] BETWEEN@startDateAND@endDateORDERBY d.Item, d.dt

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

DECLARE@startDateDATE='20140101',
    @endDateDATE='20140105';

WITH Tally(N) AS(
    SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) +1) 
        ROW_NUMBER() OVER(ORDERBY(SELECTNULL))
    FROM sys.columns a, sys.columns b
)
,CteAllDates(Item, dt) AS(
    SELECT x.Item, DATEADD(DAY, N -1, @startDate)
    FROM Tally
    CROSSJOIN(
        SELECTDISTINCT Item 
        FROM tblSales
        WHERE [Date] BETWEEN@startDateAND@endDate
    ) AS x
)
SELECT d.*, ts.Sales
FROM CteAllDates d
LEFTJOIN tblSales ts
    ON ts.Item = d.Item
    AND ts.Date = d.dt
WHERE
    ts.[Date] BETWEEN@startDateAND@endDateORDERBY 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|

Solution 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.

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

Post a Comment for "Fill Missing Dates In A Date-sequenced In Sql Using Tally Table"