SQL Date Ranges with Connect By

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