Re: Date range view

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