Forum Stats

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

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Log In Register

Categories

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.
· Share on TwitterShare on Facebook

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.
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 543079
    543079 Member Posts: 60
    Thanks Ghulam, unfortunately the query does not return 04-OCT-2007 which lies within the range.
    · Share on TwitterShare on Facebook
  • 543079
    543079 Member Posts: 60
    Hi Rob,

    Thanks for the awesome query! Any chance this can be done in v9.2?
    · Share on TwitterShare on Facebook
  • 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.
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 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?
    · Share on TwitterShare on Facebook
  • 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.
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
This discussion has been closed.