Forum Stats

  • 3,740,609 Users
  • 2,248,281 Discussions
  • 7,861,346 Comments

Discussions

Help With Range Query

543079
543079 Member Posts: 60
edited Nov 8, 2007 4:56AM in SQL & PL/SQL
Hi folks,

Let's say I have a table with this data:
START_DATE  END_DATE
----------- -----------
01-OCT-2007
02-OCT-2007 05-OCT-2007
02-OCT-2007
03-OCT-2007
03-OCT-2007
20-OCT-2007 21-OCT-2007
I would like a query that would give me each unique date including ones that fall between the start and end dates:
01-OCT-2007
02-OCT-2007
03-OCT-2007
04-OCT-2007
05-OCT-2007
20-OCT-2007
21-OCT-2007
Selecting the unique start date is of course not a problem. I am scratching my head on how to grab the list of dates that are within a range.

Please help. Thanks.

Comments

  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    If you are on 10g or higher:
    SQL> create table mytable
    2 as
    3 select date '2007-10-01' start_date, null end_date from dual union all
    4 select date '2007-10-02', date '2007-10-05' from dual union all
    5 select date '2007-10-02', null from dual union all
    6 select date '2007-10-03', null from dual union all
    7 select date '2007-10-03', null from dual union all
    8 select date '2007-10-20', date '2007-10-21' from dual
    9 /

    Tabel is aangemaakt.

    SQL> select distinct start_date
    2 from mytable
    3 model
    4 partition by (rownum r)
    5 dimension by (0 i)
    6 measures (start_date, end_date)
    7 rules
    8 ( start_date[for i from 1 to end_date[0] - start_date[0] increment 1] = start_date[0] + cv(i)
    9 )
    10 order by start_date
    11 /

    START_DATE
    -------------------
    01-10-2007 00:00:00
    02-10-2007 00:00:00
    03-10-2007 00:00:00
    04-10-2007 00:00:00
    05-10-2007 00:00:00
    20-10-2007 00:00:00
    21-10-2007 00:00:00

    7 rijen zijn geselecteerd.
    Regards,
    Rob.
  • Ghulam Mustafa Butt
    Ghulam Mustafa Butt Member Posts: 694 Blue Ribbon
    This may helps
    SELECT COL1 FROM (
    SELECT '01-OCT-2007' COL1, NULL COL2 FROM DUAL
    UNION
    SELECT '02-OCT-2007', '05-OCT-2007' FROM DUAL
    UNION
    SELECT '02-OCT-2007', NULL FROM DUAL
    UNION
    SELECT '03-OCT-2007', NULL FROM DUAL
    UNION
    SELECT '03-OCT-2007', NULL FROM DUAL
    UNION
    SELECT '20-OCT-2007', '21-OCT-2007' FROM DUAL )
    UNION
    SELECT COL2 FROM (
    SELECT '01-OCT-2007' COL1, NULL COL2 FROM DUAL
    UNION
    SELECT '02-OCT-2007', '05-OCT-2007' FROM DUAL
    UNION
    SELECT '02-OCT-2007', NULL FROM DUAL
    UNION
    SELECT '03-OCT-2007', NULL FROM DUAL
    UNION
    SELECT '03-OCT-2007', NULL FROM DUAL
    UNION
    SELECT '20-OCT-2007', '21-OCT-2007' FROM DUAL )

    COL1
    -----------
    01-OCT-2007
    02-OCT-2007
    03-OCT-2007
    05-OCT-2007
    20-OCT-2007
    21-OCT-2007
    [pre]

    HTH
    Ghulam
  • 543079
    543079 Member Posts: 60
    Thanks Ghulam, unfortunately the query does not return 04-OCT-2007 which lies within the range.
  • 543079
    543079 Member Posts: 60
    Hi Rob,

    Thanks for the awesome query! Any chance this can be done in v9.2?
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    > Any chance this can be done in v9.2?

    No, because the model clause got introduced in version 10.

    In 9.2 you can use something like this:
    SQL>  select distinct start_date + level - 1
    2 from (select t.*, rownum rn from mytable t)
    3 connect by level <= nvl(end_date,start_date) - start_date + 1
    4 and prior rn = rn
    5 and prior dbms_random.value is not null
    6 order by 1
    7 /

    START_DATE+LEVEL-1
    -------------------
    01-10-2007 00:00:00
    02-10-2007 00:00:00
    03-10-2007 00:00:00
    04-10-2007 00:00:00
    05-10-2007 00:00:00
    20-10-2007 00:00:00
    21-10-2007 00:00:00

    7 rijen zijn geselecteerd.
    Regards,
    Rob.
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    edited Oct 23, 2007 10:46AM
    SQL> with tbl as
    2 (select 1 st, 3 ed from dual union all
    3 select 2 st, null from dual union all
    4 select 5 st, null from dual union all
    5 select 8 st, 12 ed from dual)
    6 select distinct st+level-1
    7 from tbl
    8 connect by level <= ed-st+1
    9 order by st+level-1;

    ST+LEVEL-1
    ----------
    1
    2
    3
    5
    8
    9
    10
    11
    12

    9 rows selected.
    Nicolas.

    Too late.
    Message was edited by:
    N. Gasparotto
  • 543079
    543079 Member Posts: 60
    Thanks alot! Just what I needed.

    There is this line that confuses me:
    prior dbms_random.value is not null
    Please enlighten me as to what the purpose for this is. I have tried to take this out but returned with a
    ORA-01436: CONNECT BY loop in user data
    . I've also tried to replace dbms_random.value with a constant (i.e. the number 1) and generates the same error as well. From this, it's obviously used as a condition to stop an infinite loop...but how?

    Doesn't dbms_random.value always return a not null?
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    As you have found out, it's a trick to prevent the ORA-01436 indeed.

    I learned it from Volder, who explained it [url http://forums.oracle.com/forums/message.jspa?messageID=1855638#1855638]here.

    Regards,
    Rob.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    create table dayTable(StartDay,EndDay) as
    select to_date('2007/10/01','yyyy/mm/dd'),to_date(null) from dual union all
    select to_date('2007/10/02','yyyy/mm/dd'),to_date('2007/10/05','yyyy/mm/dd') from dual union all
    select to_date('2007/10/03','yyyy/mm/dd'),to_date(null) from dual union all
    select to_date('2007/10/03','yyyy/mm/dd'),to_date(null) from dual union all
    select to_date('2007/10/20','yyyy/mm/dd'),to_date('2007/10/21','yyyy/mm/dd') from dual union all
    select to_date('2007/12/31','yyyy/mm/dd'),to_date('2007/12/31','yyyy/mm/dd') from dual;
    create or replace type OutputDayType as table of date;
    /
    create or replace function PrintOutputDayType return OutputDayType PipeLined IS
    begin
    for rec in (select StartDay,EndDay
    from dayTable
    order by StartDay) Loop
            if rec.EndDay is null then
    pipe row(rec.StartDay);
    else
    for i in 0..rec.EndDay-rec.StartDay Loop
    pipe row(rec.StartDay+i);
    end Loop;
    end if;
    end loop;
    end;
    /
    sho err
    create or replace view PrintOutputView(day1) as
    select distinct *
    from table(PrintOutputDayType)
    with read only;
    select day1
    from PrintOutputView
    order by day1;
    DAY1
    --------
    07-10-01
    07-10-02
    07-10-03
    07-10-04
    07-10-05
    07-10-20
    07-10-21
    07-12-31
This discussion has been closed.