Re: Date range view

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 14 Nov 2008 17:56:58 +0100
Message-ID: <491dadd8$0$20774$426a74cc@news.free.fr>

<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 Received on Fri Nov 14 2008 - 10:56:58 CST