 | |
#1
 
| |||
| |||
|
Date range view -
11-14-2008
, 07:41 AM
#2
| |||
| |||
|
|
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
#3
| |||
| |||
|
|
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
#4
| |||
| |||
|
|
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
#5
| |||
| |||
|
|
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
#6
| |||
| |||
|
|
dleland3 (AT) gmail (DOT) com> a écrit dans le message de news: 6690ecf1-7f0d-4ca0-bb14-f445eee2cb82...oglegroups.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 |
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
#7
| |||
| |||
|
|
dleland3 (AT) gmail (DOT) com> a écrit dans le message de news: 6690ecf1-7f0d-4ca0-bb14-f445eee2cb82...oglegroups.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 |
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
#8
| |||
| |||
|
|
dleland3 (AT) gmail (DOT) com> a écrit dans le message de news: 6690ecf1-7f0d-4ca0-bb14-f445eee2cb82...oglegroups.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 |
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
#9
| |||
| |||
|
|
dleland3 (AT) gmail (DOT) com> a écrit dans le message de news: 6690ecf1-7f0d-4ca0-bb14-f445eee2cb82...oglegroups.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 |
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
#10
| |||
| |||
|
|
dlela... (AT) gmail (DOT) com> a écrit dans le message de news: 6690ecf1-7f0d-4ca0-bb14-f445eee2c... (AT) l33g2000pri (DOT) 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 |
When I run it, I receive the following error:
ERROR at line 12:
ORA-00933: SQL command not properly ended
I'm running Oracle 9i. Any ideas?
Dave
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.