Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    10

    Unanswered: How to create a date interval ?

    Hi,

    i have a request that returns counts of data for each days, but i need to have results returned for each days even if it's 0, as i need to process a chart, i would like to have the specific period of time without any 'holes', like this:
    count(*) - date
    5 - 2003/07/11
    2 - 2003/07/12
    0 - 2003/07/13
    9 - 2003/07/14
    and not that
    count(*) - date
    5 - 2003/07/11
    2 - 2003/07/12
    9 - 2003/07/14

    thanks, Julien

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How to create a date interval ?

    Originally posted by gomezo
    Hi,

    i have a request that returns counts of data for each days, but i need to have results returned for each days even if it's 0, as i need to process a chart, i would like to have the specific period of time without any 'holes', like this:
    count(*) - date
    5 - 2003/07/11
    2 - 2003/07/12
    0 - 2003/07/13
    9 - 2003/07/14
    and not that
    count(*) - date
    5 - 2003/07/11
    2 - 2003/07/12
    9 - 2003/07/14

    thanks, Julien
    You need a table or view that has EVERY required date in it like this:

    date_val
    2003/07/11
    2003/07/12
    2003/07/13
    2003/07/14

    Then you can outer join the real table to it like this:

    Code:
    select date_tab.date_val, count(real_tab.date_val)
    from real_tab, date_tab
    where real_tab.date_val (+)= date_tab.date_val
    group by date_tab.date_val;
    Note: cannot use COUNT(*) otherwise you will get a result of 1 for "missing" dates.

    You could either create and populate date_tab, or you can "fake" it like this:

    Code:
    select date_tab.date_val, count(real_tab.date_val)
    from real_tab,
            ( select to_date('2003/07/10','YYYY/MM/DD')+rownum as date_val
              from all_objects
              where rownum <= 4
            ) date_tab
    where real_tab.date_val (+)= date_tab.date_val
    group by date_tab.date_val;
    I used ALL_OBJECTS because it always has quite a lot of rows. You can use any table or view you like, as long as it has enough rows.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •