Fill Missing Dates In A Date-sequenced In Sql Using Tally Table
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-JOIN
ed to tblSales
to achieve the desired output.
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 CTE
s, 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"