Re: Date range view
From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 14 Nov 2008 19:53:17 +0100
Message-ID: <gfkhes$qcf$03$1@news.t-online.com>
> |I am currently using Oracle 9i and have created a table:
> |
> | CREATE TABLE FSR_TECH
> | (
> | ID VARCHAR2(20),
> | TECHNICIAN VARCHAR2(20),
> | FROM_DATE DATE,
> | THRU_DATE DATE
> | )
> |
> | I have inserted the following data into it:
> |
> | INSERT INTO FSR_TECH VALUES('FSR0000001','JIM.POULSEN','30-
> | SEP-2008','01-OCT-2008');
> | INSERT INTO FSR_TECH VALUES('FSR0000002','DAVE.ROSE','13-OCT-2008','13-
> | OCT-2008');
> | INSERT INTO FSR_TECH VALUES('FSR0000002', 'LLOYD.MEHANEY', '13-
> | OCT-2008', '13-OCT-2008');
> | INSERT INTO FSR_TECH VALUES('FSR0000003', 'DAVE.ROSE', '06-OCT-2008',
> | '13-OCT-2008');
> | COMMIT;
> |
> | ID TECHNICIAN FROM_DATE THRU_DATE
> | ---------- -------------------- ---------- ----------
> | FSR0000001 JIM.POULSEN 2008-09-30 2008-10-01
> | FSR0000002 DAVE.ROSE 2008-10-13 2008-10-13
> | FSR0000002 LLOYD.MEHANEY 2008-10-13 2008-10-13
> | FSR0000003 DAVE.ROSE 2008-10-06 2008-10-13
> |
> | I would like to create a view over it by including all dates between
> | the from and thru dates
> | (inclusive) and have it presented as follows:
> |
> | TECHNICIAN DATE
> | ------------- ----------
> | JIM.POULSEN 2008-09-30
> | JIM.POULSEN 2008-10-01
> | DAVE.ROSE 2008-10-13
> | LLOYD.MEHANEY 2008-10-13
> | DAVE.ROSE 2008-10-06
> | DAVE.ROSE 2008-10-07
> | DAVE.ROSE 2008-10-08
> | DAVE.ROSE 2008-10-09
> | DAVE.ROSE 2008-10-10
> | DAVE.ROSE 2008-10-11
> | DAVE.ROSE 2008-10-12
> | DAVE.ROSE 2008-10-13
> |
> | I have tried numerous things (CONNECT BY, LEVEL, PARTITION) but just
> | cannot come up with a way of doing it. Any ideas?
> |
> | Dave
Date: Fri, 14 Nov 2008 19:53:17 +0100
Message-ID: <gfkhes$qcf$03$1@news.t-online.com>
Michel Cadot schrieb:
> <dleland3_at_gmail.com> a écrit dans le message de news: 6690ecf1-7f0d-4ca0-bb14-f445eee2cb82_at_l33g2000pri.googlegroups.com...
> |I am currently using Oracle 9i and have created a table:
> |
> | CREATE TABLE FSR_TECH
> | (
> | ID VARCHAR2(20),
> | TECHNICIAN VARCHAR2(20),
> | FROM_DATE DATE,
> | THRU_DATE DATE
> | )
> |
> | I have inserted the following data into it:
> |
> | INSERT INTO FSR_TECH VALUES('FSR0000001','JIM.POULSEN','30-
> | SEP-2008','01-OCT-2008');
> | INSERT INTO FSR_TECH VALUES('FSR0000002','DAVE.ROSE','13-OCT-2008','13-
> | OCT-2008');
> | INSERT INTO FSR_TECH VALUES('FSR0000002', 'LLOYD.MEHANEY', '13-
> | OCT-2008', '13-OCT-2008');
> | INSERT INTO FSR_TECH VALUES('FSR0000003', 'DAVE.ROSE', '06-OCT-2008',
> | '13-OCT-2008');
> | COMMIT;
> |
> | ID TECHNICIAN FROM_DATE THRU_DATE
> | ---------- -------------------- ---------- ----------
> | FSR0000001 JIM.POULSEN 2008-09-30 2008-10-01
> | FSR0000002 DAVE.ROSE 2008-10-13 2008-10-13
> | FSR0000002 LLOYD.MEHANEY 2008-10-13 2008-10-13
> | FSR0000003 DAVE.ROSE 2008-10-06 2008-10-13
> |
> | I would like to create a view over it by including all dates between
> | the from and thru dates
> | (inclusive) and have it presented as follows:
> |
> | TECHNICIAN DATE
> | ------------- ----------
> | JIM.POULSEN 2008-09-30
> | JIM.POULSEN 2008-10-01
> | DAVE.ROSE 2008-10-13
> | LLOYD.MEHANEY 2008-10-13
> | DAVE.ROSE 2008-10-06
> | DAVE.ROSE 2008-10-07
> | DAVE.ROSE 2008-10-08
> | DAVE.ROSE 2008-10-09
> | DAVE.ROSE 2008-10-10
> | DAVE.ROSE 2008-10-11
> | DAVE.ROSE 2008-10-12
> | DAVE.ROSE 2008-10-13
> |
> | I have tried numerous things (CONNECT BY, LEVEL, PARTITION) but just
> | cannot come up with a way of doing it. Any ideas?
> |
> | Dave
> > > SQL> with > 2 minmax as ( > 3 select min(from_date) mindate, max(thru_date) maxdate > 4 from fsr_tech > 5 ), > 6 cal as ( > 7 select mindate+level-1 mydate > 8 from minmax > 9 connect by level <= maxdate-mindate+1 > 10 ) > 11 select id, technician, mydate > 12 from fsr_tech partition by (id, technician) > 13 left outer join cal > 14 on (mydate between from_date and thru_date) > 15 order by 1,3 > 16 / > ID TECHNICIAN MYDATE > -------------------- -------------------- ----------- > FSR0000001 JIM.POULSEN 30-SEP-2008 > FSR0000001 JIM.POULSEN 01-OCT-2008 > FSR0000002 DAVE.ROSE 13-OCT-2008 > FSR0000002 LLOYD.MEHANEY 13-OCT-2008 > FSR0000003 DAVE.ROSE 06-OCT-2008 > FSR0000003 DAVE.ROSE 07-OCT-2008 > FSR0000003 DAVE.ROSE 08-OCT-2008 > FSR0000003 DAVE.ROSE 09-OCT-2008 > FSR0000003 DAVE.ROSE 10-OCT-2008 > FSR0000003 DAVE.ROSE 11-OCT-2008 > FSR0000003 DAVE.ROSE 12-OCT-2008 > FSR0000003 DAVE.ROSE 13-OCT-2008 > > 12 rows selected. > > Regards > Michel > >
Michel, iirc , partition join doesn't work on 9i, but it can be safely
left out in this case as well.
Alternatively, one hierarchical query should be sufficient as well
SQL> select id,technician,from_date + level - 1 mydate
2 from fsr_tech t
3 connect by prior id=id
4 and prior technician=technician
5 and prior from_date + level -1 <= thru_date
6 and prior dbms_random.value is not null
7 ;
ID TECHNICIAN MYDATE -------------------- -------------------- ---------- FSR0000001 JIM.POULSEN 2008-09-30 FSR0000001 JIM.POULSEN 2008-10-01 FSR0000002 DAVE.ROSE 2008-10-13 FSR0000002 LLOYD.MEHANEY 2008-10-13 FSR0000003 DAVE.ROSE 2008-10-06 FSR0000003 DAVE.ROSE 2008-10-07 FSR0000003 DAVE.ROSE 2008-10-08 FSR0000003 DAVE.ROSE 2008-10-09 FSR0000003 DAVE.ROSE 2008-10-10 FSR0000003 DAVE.ROSE 2008-10-11 FSR0000003 DAVE.ROSE 2008-10-12 FSR0000003 DAVE.ROSE 2008-10-13
12 rows selected.
Best regards
Maxim Received on Fri Nov 14 2008 - 12:53:17 CST