Oracle 10g SQL - List of all dates between 2 dates

jamiepryer
jamiepryer used Ask the Experts™
on
Hi All,

I have a problem, where I am trying to show daily activity, however not all days have something happening on them.
So for these "0" days, I want to show them in a table still but im not sure how.

Table:
Date - Sold
-----------------
10/11/2012 - Apple
10/11/2012 - Apple
11/11/2012 - Apple
13/11/2012 - Apple
15/12/2012 - Apple

So the above is across 5 days, however only days 10, 11, 13 have activity.
I want the results to show something along the lines of:

Date - Total
10 - 2
11 - 1
12 - 0
13 - 1
14 - 0
15 - 1

Ive got this working well already, however not for the "0" days.

NB. This will be across months and years.


SO ive got the following SQL which does this nicely, however ive no idea how to get this pulling the min-max date from my table

SELECT
      TO_DATE('12/04/2012', 'dd/mm/yyyy') - 1 + rownum AS d,
FROM all_data
WHERE TO_DATE('12/04/2012', 'dd/mm/yyyy') - 1 + rownum <= to_date(to_char(sysdate,'dd/mm/yyyy'),'dd/mm/yyyy')

ive tried doing this, but it doesnt work:


SELECT
      TO_DATE(start_date, 'dd/mm/yyyy') - 1 + rownum AS d
FROM
      (
            select distinct
                  max(to_date(to_char(rCC.creation_date,'dd/mm/yyyy'),'dd/mm/yyyy')) as end_date,
                  min(to_date(to_char(rCC.creation_date,'dd/mm/yyyy'),'dd/mm/yyyy')) as start_date
            from all_data rCC
            where rc.food = apple
      )
WHERE TO_DATE(start_date, 'dd/mm/yyyy') - 1 + rownum <= to_date(to_char(end_date),'dd/mm/yyyy')
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
I assume there is a typo in your sample data,  you actually have a 36 day range

November 10 to December 15

I assume that's supposed to be all November data.

try this...

SELECT   d, COUNT(food)
    FROM     (SELECT     start_date + LEVEL - 1 d
                    FROM (SELECT MIN(creation_date) start_date, MAX(creation_date) end_date
                            FROM yourtable)
              CONNECT BY start_date + LEVEL - 1 <= end_date)
         LEFT JOIN
             yourtable
         ON d = creation_date
GROUP BY d
ORDER BY d

Author

Commented:
Hero!
thanks a lot, works a treat!

can you talk me through the LEVEL please?
ive not seen that before?

sorry to be a pain

Author

Commented:
perfect - just what i needed!
Most Valuable Expert 2011
Top Expert 2012

Commented:
here's a simpler version

select level from dual connect by level < 10

I'm just setting up a counter so I can iterate through every day.
Normally connect by is used to create hierarchical queries but as a side effect it can be used to generate a counting result set

Author

Commented:
thanks

is there any other way, apart from using connect by

MY SQL works fine in Squirrel, however when i move over to my reporting tool, within an applications UI, its kicking out a "java.sql.SQLException: ORA-00923: FROM keyword not found where expected" error

when i break it down, its caused by the connect by section....

Author

Commented:
nb. im using Oracle Database 11g
Most Valuable Expert 2011
Top Expert 2012

Commented:
don't use squirrel  :)

less efficient,  query against a table with at least as many rows as the number of days you'll need to span

I'm using all_objects below, I recommend picking some real table you have access to


SELECT   d, COUNT(food)
    FROM     (SELECT start_date + ROWNUM - 1 d
                FROM (SELECT MIN(creation_date) start_date, MAX(creation_date) end_date
                        FROM yourtable),
                     all_objects
               WHERE start_date + ROWNUM - 1 <= end_date)
         LEFT JOIN
             yourtable
         ON d = creation_date
GROUP BY d
ORDER BY d

Author

Commented:
im confused sorry
my SQL works fine in Squirrel and SQLDeveloper, however not in my application UI.

so not sure why i shouldnt use those?
Most Valuable Expert 2011
Top Expert 2012

Commented:
sorry, I misread your comment I thought you said it didn't work in squirrel.

I don't know what to say about the application then.  The query works, as you've seen.

If the application is failing, is it doing everything within an 11g db?  Does it have any components on older databases accessed through links? or external code running on older clients?

The clients shouldn't matter, but something is trying to parse the code in a way that isn't legal.

another thought,  you must have modified the query at least a little when you put it into your app,  did you introduce a syntax error somewhere?  and extra () or comma somewhere?

Author

Commented:
ok thanks

i will have to find out

i had to an some extra () but then i copied from my app and back to Squirrel and it still worked fine.

might be bug as per; http://stackoverflow.com/questions/4546559/subquery-works-in-9i-but-not-in-11g

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial