Oracle 10g query to find missing month/year based on one data element for a date range.
Posted on 2011-10-19
I am trying to write an oracle query that will find the missing month and year based on a date range with one data element. I want to join it into a union statement with an already existing query.
The first SQL that is being used is as follows:
FROM PR A
WHERE A.DATE_CREATED >= TO_DATE('01/01/2011','MM/DD/YYYY')
AND A.DATE_CREATED <= TO_DATE('12/31/2011','MM/DD/YYYY')
This pulls the following example data:
Basically I'm looking for a union statement that would find the missing months and year between the date range. The day could be set to the 1st so the result set would fill in the missing month and year:
Any help would be much appreciated!