A question appeared in one of the OTN forums:
http://forums.oracle.com/forums/thread.jspa?messageID=3891491
—
USER_NAME FROM_DATE TO_DATE COMMENTS -------------------------------------------------------------------------------- Uma Shankar 2-Nov-09 5-Nov-09 Comment1 Veera 11-Nov-09 13-Nov-09 Comment2 Uma Shankar 15-Dec-09 17-Dec-09 Commnet3 Vinod 20-Oct-09 21-Oct-09 Comments4
The above table is the user leave calendar. Now I need to display the users who are on leave between 01-Nov-2009 to 30-Nov-2009. The output should look like:
USER_NAME FROM_DATE COMMENTS -------------------------------------------------------------------------------- Uma Shankar 2-Nov-09 Comment1 Uma Shankar 3-Nov-09 Comment1 Uma Shankar 4-Nov-09 Comment1 Uma Shankar 5-Nov-09 Comment1 Veera 11-Nov-09 Comment2 Veera 12-Nov-09 Comment2 Veera 13-Nov-09 Comment2
—
How would one go about finding a solution for the above problem? Possibly by breaking the problem into smaller steps.
Here is a test set up:
CREATE TABLE T1( USERNAME VARCHAR2(30), FROM_DATE DATE, TO_DATE DATE, COMMENTS VARCHAR2(100)); INSERT INTO T1 VALUES ('Uma Shankar', '02-Nov-09','05-Nov-09','Comment1'); INSERT INTO T1 VALUES ('Veera','11-Nov-09','13-Nov-09','Comment2'); INSERT INTO T1 VALUES ('Uma Shankar','15-Dec-09','17-Dec-09','Commnet3'); INSERT INTO T1 VALUES ('Vinod','20-Oct-09','21-Oct-09','Comments4'); INSERT INTO T1 VALUES ('Mo','20-Oct-09','05-NOV-09','Comments4'); COMMIT;
Note that I included one additional row, where the person starts their vacation in the previous month and ends in the month of November. You could approach the problem like this:
Assume that you would like to list all of the days of a particular month:
SELECT TO_DATE('01-NOV-2009','DD-MON-YYYY')+(ROWNUM-1) MONTH_DAY FROM DUAL CONNECT BY LEVEL<=ADD_MONTHS(TO_DATE('01-NOV-2009','DD-MON-YYYY'),1) -TO_DATE('01-NOV-2009','DD-MON-YYYY');
Note that the above attempts to calculate the number of days in the month of November – if it is known that the month has a particular number of days, 30 for instance, you could rewrite the CONNECT BY clause like this:
CONNECT BY LEVEL<=30
Now, we need to pick up those rows of interest from the table:
SELECT * FROM T1 T WHERE (T.FROM_DATE BETWEEN TO_DATE('01-NOV-2009','DD-MON-YYYY') AND TO_DATE('30-NOV-2009','DD-MON-YYYY') OR T.TO_DATE BETWEEN TO_DATE('01-NOV-2009','DD-MON-YYYY') AND TO_DATE('30-NOV-2009','DD-MON-YYYY')); USERNAME FROM_DATE TO_DATE COMMENTS --------------- --------- --------- ---------- Uma Shankar 02-NOV-09 05-NOV-09 Comment1 Veera 11-NOV-09 13-NOV-09 Comment2 Mo 20-OCT-09 05-NOV-09 Comments4
If we then join the two resultsets, we have the following query:
SELECT * FROM T1 T, (SELECT TO_DATE('01-NOV-2009','DD-MON-YYYY')+(ROWNUM-1) MONTH_DAY FROM DUAL CONNECT BY LEVEL<=ADD_MONTHS(TO_DATE('01-NOV-2009','DD-MON-YYYY'),1)-TO_DATE('01-NOV-2009','DD-MON-YYYY')) V WHERE (T.FROM_DATE BETWEEN TO_DATE('01-NOV-2009','DD-MON-YYYY') AND TO_DATE('30-NOV-2009','DD-MON-YYYY') OR T.TO_DATE BETWEEN TO_DATE('01-NOV-2009','DD-MON-YYYY') AND TO_DATE('30-NOV-2009','DD-MON-YYYY')) AND V.MONTH_DAY BETWEEN T.FROM_DATE AND T.TO_DATE ORDER BY USERNAME, MONTH_DAY; USERNAME FROM_DATE TO_DATE COMMENTS MONTH_DAY --------------- --------- --------- ---------- --------- Mo 20-OCT-09 05-NOV-09 Comments4 01-NOV-09 Mo 20-OCT-09 05-NOV-09 Comments4 02-NOV-09 Mo 20-OCT-09 05-NOV-09 Comments4 03-NOV-09 Mo 20-OCT-09 05-NOV-09 Comments4 04-NOV-09 Mo 20-OCT-09 05-NOV-09 Comments4 05-NOV-09 Uma Shankar 02-NOV-09 05-NOV-09 Comment1 02-NOV-09 Uma Shankar 02-NOV-09 05-NOV-09 Comment1 03-NOV-09 Uma Shankar 02-NOV-09 05-NOV-09 Comment1 04-NOV-09 Uma Shankar 02-NOV-09 05-NOV-09 Comment1 05-NOV-09 Veera 11-NOV-09 13-NOV-09 Comment2 11-NOV-09 Veera 11-NOV-09 13-NOV-09 Comment2 12-NOV-09 Veera 11-NOV-09 13-NOV-09 Comment2 13-NOV-09
The WHERE clause should be
WHERE
(T.FROM_DATE BETWEEN TO_DATE(’01-NOV-2009′,’DD-MON-YYYY’) AND TO_DATE(’30-NOV-2009′,’DD-MON-YYYY’)
OR T.TO_DATE BETWEEN TO_DATE(’01-NOV-2009′,’DD-MON-YYYY’) AND TO_DATE(’30-NOV-2009′,’DD-MON-YYYY’)
OR (T.FROM_DATE TO_DATE(’30-NOV-2009′,’DD-MON-YYYY’)))
AND V.MONTH_DAY BETWEEN T.FROM_DATE AND T.TO_DATE
else, it will not catch the vacations that begin in October and lasts in December.