Re: Date range view

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 14 Nov 2008 20:52:40 +0100
Message-ID: <491dd708$0$21634$426a74cc@news.free.fr>

"Maxim Demenko" <mdemenko_at_gmail.com> a écrit dans le message de news: gfkhes$qcf$03$1_at_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

Yes you are right Maxim I didn't notice OP's version, just the fact he mentioned he tested PARTITION.

Note that if your query works in 9.2 it no more works in 10.2.0.4 with new connect by algorithm:
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 /
ERROR:
ORA-01436: CONNECT BY loop in user data

Regards
Michel Received on Fri Nov 14 2008 - 13:52:40 CST

Original text of this message