Re: Date range view
Date: Fri, 14 Nov 2008 12:27:20 -0800 (PST)
Message-ID: <de344d2e-8b84-4229-a539-d9071ae68a5b@z28g2000prd.googlegroups.com>
On Nov 14, 1:53 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> Michel Cadot schrieb:
>
>
>
>
>
> > <dlela..._at_gmail.com> a écrit dans le message de news: 6690ecf1-7f0d-4ca0-bb14-f445eee2c..._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- Hide quoted text -
>
> - Show quoted text -
Thanks. That works perfectly.
Dave Received on Fri Nov 14 2008 - 14:27:20 CST