Date range view
Date: Fri, 14 Nov 2008 05:41:55 -0800 (PST)
Message-ID: <6690ecf1-7f0d-4ca0-bb14-f445eee2cb82@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 Received on Fri Nov 14 2008 - 07:41:55 CST