Fill In Missing Dates
Fill In Missing Dates
(OP)
I have a SQL query that is pulling data from my sales table, showing total sales by day. My problem is that some days our store is not open and there is no data for these dates (thus I get a result set that looks like this):
DATE SALES
1/1 4554.44
1/2 4847.54
1/5 5824.54
1/6 4885.43
1/7 5775.35
I still want records to show a zero where there were no sales (like this):
DATE SALES
1/1 4554.44
1/2 4847.54
1/3 0
1/4 0
1/5 5824.54
1/6 4885.43
1/7 5775.35
How can I do this?
DATE SALES
1/1 4554.44
1/2 4847.54
1/5 5824.54
1/6 4885.43
1/7 5775.35
I still want records to show a zero where there were no sales (like this):
DATE SALES
1/1 4554.44
1/2 4847.54
1/3 0
1/4 0
1/5 5824.54
1/6 4885.43
1/7 5775.35
How can I do this?
Talk To Other Members
RE: Fill In Missing Dates
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com
RE: Fill In Missing Dates
SELECT * FROM SALES WHERE TRANSDATE>=20060101
RE: Fill In Missing Dates
Create a table with all dates (AllDates) and populate it with all the dates in the range you are interested in.... (You might want to save this for future use... handy as all get out.)
Select AD.TheDate, Sum(S.Sales)
From AllDates as AD Left outer Join AD.TheDate = S.Date
Group by AD.TheDate