RE: SQL and displaying date ranges

[Ian Skinner]

I would try and do an left or right outer join on an INLINE table that creates a record for every day in a date range whether their is a sales value or not.  Then the table only exists while the query is running.  Trouble is I'm not good enough with my DBMS sql functions to know how to do this off the top of my head.  I would have to crack open a book to get to find the correct date functions and commands.

--------------
Ian Skinner
Web Programmer
BloodSource
www.BloodSource.org
Sacramento, CA

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 28, 2004 11:36 AM
To: CF-Talk
Subject: OT: SQL and displaying date ranges

Quick question:

I have a database containing sales information... I'm using CFCHART to
display a bar graph showing the sales for each day.

My problem is... If there are no sales for the day... There is no day in
the query... So the graph just "skips" a day.  Obviously this makes for
an inaccurate graph.

I thought about looping over each day in the date range and then
checking the query to see if there's any sales data... But that seems
painfully inefficient.  I also thought about some type of left join on a
temporary database table or something like that... But once again...
Seems inefficient.

I'm curious how other's query for such information and display accurate
graphs.

The example I gave above isn't exactly the case... But I'm trying to
keep this simple so see what sort of input everyone has.

-Novak
  _____

---

[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

---