Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Fill In Missing Dates

Status
Not open for further replies.
byrne1

byrne1

Programmer
Aug 7, 2001
415
US
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?
 
Sort by date Sort by votes
What does your existing query look like? Also, what version of PSQL are you using?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
 
Upvote 0 Downvote
Version 10, and I'm basically saying:

SELECT * FROM SALES WHERE TRANSDATE>=20060101

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dgillz
  • Locked
  • Question
How to Execute Create View code in Actian Zen?
Replies
1
Views
526
mirtheil
mirtheil
profoundhypnotic
  • Locked
  • Question
How to read a csv file in Pervasive?
Replies
1
Views
577
mirtheil
mirtheil
ianbtheog
  • Locked
  • Question
Connection Manager is not running at the specified port number - Pervasive 13.31.014 - Intermittent
Replies
2
Views
545
ianbtheog
ianbtheog
Alvin Chong
  • Locked
  • Question
Invisible Space In my PSQL database
Replies
7
Views
604
Alvin Chong
Alvin Chong
Ino Mart
  • Locked
  • Question
How to use a variable as table name in a procedure 1
Replies
2
Views
439
Ino Mart
Ino Mart

Part and Inventory Search

Sponsor

Back
Top