RE: SQL and displaying date ranges

[Ian Skinner]

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]

---