Thanks for the tip. I'm using MS SQL 2000 so I guess I'll have to do
some digging.
Does anyone else out there have an MS SQL 2000 example... or another
suggestion? It just seems like such a simple every day type of thing to
me. I'm surprised there wasn't more feedback on the topic.
-Novak
-----Original Message-----
From: Ian Skinner [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 28, 2004 2:06 PM
To: CF-Talk
Subject: RE: SQL and displaying date ranges
This is a very ORACLE specific example. This kind of functionality gets
heavily into the specific capabilities of the DBMS system you are using.
But this should select all the invoices from a date range of 12/1/2003
to 12/5/2003 (hard coded in this example), returning an empty row if no
invoice exists, I may have the outer join on the wrong side I don't have
anything to easily test this SQL on.
SELECT
INVOICE_TABLE.INVOICES,
INVOICE_TABLE.DATES,
ALL_DAYS.DAYS
FROM
INVOICE_TABLE,
( SELECT
TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum AS DAYS
FROM
all_objects
WHERE
TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum <=
TO_DATE('12/05/2003', 'MM/DD/YYYY')
) AS ALL_DAYS
WHERE
INVOICE_TABLE.DATES = ALL_DAYS.DAYS(+)
HTH
--------------
Ian Skinner
Web Programmer
BloodSource
www.BloodSource.org
Sacramento, CA
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 28, 2004 11:58 AM
To: CF-Talk
Subject: RE: SQL and displaying date ranges
I wouldn't mind seeing an example of that if you have the time.
-Novak
-----Original Message-----
From: Ian Skinner [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 28, 2004 11:43 AM
To: CF-Talk
Subject: RE: SQL and displaying date ranges
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]
