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:

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:

Related

Actions

Information

One response

28052013

Francisco Villarinho(21:14:53) :

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.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: