Display Distinct Dates Not Exist In Table

I have a table called maximo_products and have a field called product_date.

I need to display all distinct dates which are not in in maximo_products table in MON-YYYY format between Jan-2014 and Dec-2014.

How can do this?

SQLFiddle with table structure and records.

Sample output (dates which are not available in table)

MAR-2014
APR-2014
JUN-2014
JUL-2014    
SEP-2014

Answers


To get the missing months, you need to generate all the months. The following uses a simple formulation for getting 12 months. It then uses not in to figure out which have no values:

with mons as (
      select rownum r, add_months('01-JAN-2014', rownum - 1) as mon
      from dual
      connect by rownum <= 12
     )
select *
from mons 
where not exists (select 1
                  from maximo_products mp
                  where to_char(mp.product_date, 'YYYY-MM') = 
                        to_char(mons.mon, 'YYYY-MM')
                 )
order by r;

EDIT:

You can move the definition of mons into a subquery:

select *
from (select rownum r, add_months('01-JAN-2014', rownum - 1) as mon
      from dual
      connect by rownum <= 12
     ) mons 
where not exists (select 1
                  from maximo_products mp
                  where to_char(mp.product_date, 'YYYY-MM') = 
                        to_char(mons.mon, 'YYYY-MM')
                 )
order by r;

Need Your Help

Select distinct set of values from related table in NHibernate

c# nhibernate nhibernate-criteria

Sorry I'm a newbie with NHibernate I hope I don't embarrass myself with this question.

Only some users reporting "Resource Not Found" error. Does this make sense?

java android jodatime crittercism

I am seeing a a couple of errors coming up on Crittercism (Crash reporting service) for my published Android app. The trace is the following: