Fill In Missing Dates

[byrne1]

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?

 

[mirtheil]

What does your existing query look like? Also, what version of PSQL are you using?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[byrne1]

Version 10, and I'm basically saying:

SELECT * FROM SALES WHERE TRANSDATE>=20060101

 

[Peager]

Try something like this....

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