[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1050
  • Last Modified:

Oracle 10g query to find missing month/year based on one data element for a date range.

Hello experts-

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:
SELECT
A.ID
, A.DATE_CREATED
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:
ID     DATE_CREATED
11     01/20/2011
22     03/15/2011
37     04/21/2011
46     05/3/2011
47     07/12/2011

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:

ID     DATE_MISSING
1     2/1/2011
2     6/1/2011
3     8/1/2011
4     9/1/2011
5     10/1/2011
6     11/1/2011
7     12/1/2011

Any help would be much appreciated!
Thanks-
Luke
0
LukeSteele
Asked:
LukeSteele
1 Solution
 
ajcheung78Commented:
Here is an example with some data that I have embedded:
-- GET LIST OF DATES FOR THE END OF MONTH
select x.year, x.month
from
(
  select
      to_char(add_months(to_date('01/01/2011', 'DD/MM/RRRR'), x.l-1), 'YYYY') year
    , to_char(add_months(to_date('01/01/2011', 'DD/MM/RRRR'), x.l-1), 'MM') month
    , 1 day
  from dual, (select l from (select level l from dual connect by level <= 12)) x -- 12 MONTHS
) x
left join 
(
  select * from
  (
    select 11 id, to_date('01/20/2011', 'mm/dd/yyyy') a_date from dual union
    select 22 id, to_date('03/15/2011', 'mm/dd/yyyy') a_date from dual union
    select 37 id, to_date('04/21/2011', 'mm/dd/yyyy') a_date from dual union
    select 46 id, to_date('05/3/2011', 'mm/dd/yyyy') a_date from dual union
    select 47 id, to_date('07/12/2011', 'mm/dd/yyyy') a_date from dual
  )
) y
on x.year = to_char(y.a_date, 'YYYY')
  and x.month = to_char(y.a_date, 'MM')
where id is null
order by x.year, x.month

Open in new window



Here is an example which you should be able to run as is:
-- GET LIST OF DATES FOR THE END OF MONTH
select x.year, x.month
from
(
  select
      to_char(add_months(to_date('01/01/2011', 'DD/MM/RRRR'), x.l-1), 'YYYY') year
    , to_char(add_months(to_date('01/01/2011', 'DD/MM/RRRR'), x.l-1), 'MM') month
    , 1 day
  from dual, (select l from (select level l from dual connect by level <= 12)) x -- 12 MONTHS
) x
left join 
(
    SELECT
    A.ID
    , A.DATE_CREATED
    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')
) y
on x.year = to_char(y.DATE_CREATED, 'YYYY')
  and x.month = to_char(y.DATE_CREATED, 'MM')
where id is null
order by x.year, x.month

Open in new window


If you want a finer date range then you can add the date syntax to table "X".  If you want more time or different date range then change the starting date of 1/1/2011 and adjust the number of months as necessary (it is set to 12 months currently).
0
 
ajcheung78Commented:
You will end up with results like the following:

Year      Month
2011      02
2011      06
2011      08
2011      09
2011      10
2011      11
2011      12
0
 
gajmpCommented:
select
      to_char(add_months(to_date('01/01/2011', 'DD/MM/RRRR'), x.l-1), 'YYYY') year
    , to_char(add_months(to_date('01/01/2011', 'DD/MM/RRRR'), x.l-1), 'MM') month    
from (select level l from dual connect by level <= 12) x
minus
SELECT
to_char(A.DATE_CREATED, 'YYYY'),
to_char(A.DATE_CREATED, 'MM')
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')
group by to_char(A.DATE_CREATED, 'YYYY'),
to_char(A.DATE_CREATED, 'MM')
0
 
slightwv (䄆 Netminder) Commented:
The above SQL can be simplified a little.

Check out the small test case below.
drop table tab1 purge;
create table tab1(date_created date);

insert into tab1 values(to_date('01/20/2011', 'mm/dd/yyyy'));
insert into tab1 values(to_date('03/15/2011', 'mm/dd/yyyy'));
insert into tab1 values(to_date('04/21/2011', 'mm/dd/yyyy'));
insert into tab1 values(to_date('05/3/2011', 'mm/dd/yyyy'));
insert into tab1 values(to_date('07/12/2011', 'mm/dd/yyyy'));
commit;


select
      add_months(to_date('01/2011', 'MM/RRRR'), level-1)
from dual connect by level <= 12
minus
select trunc(date_created, 'MM') from tab1
/

Open in new window

0
 
LukeSteeleAuthor Commented:
Hi ajcheung78-

Thank you very much for this code snippet, it works perfectly!  I had to integrate the rest of the code into it to make it work but the results turned out perfect!  I was able to embed that code into Crystal which in turn created the months with blank values.

Thanks-
Luke
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now