CODEWITHSUNDEEP
sqloracle-database
vijaya
vijaya

Reputation: 335

How to add missing months in each year in oracle

This is the output from my query

image

but I need all months in 2016 and 2017 in the output means

01-2017,02-2017,03-2017,04-2017,05-2017, 06-2017, 07-2017, 08-2017,09-2017,10-2017,11-2017,12-2017,01-2016,01-2016,01-2016,02-2016,03-2016,04-2016,05-2016,06-2016,07-2016,08-2016,09-2016,10-2016,11-2016,12-2016]

my query is:

SELECT DISTINCT
    to_char(to_date(checked_date, 'dd-mon-yy'), 'mm-yyyy') AS month
FROM DOCTOR_CHECKUP;

The below image shows the output of my query, but I need all months in 2016 and 2017 in the output means 01-2017,02-2017,03-2017,04-2017,05-2017, 06-2017, 07-2017, 08-2017,09-2017,10-2017,11-2017,12-2017,01-2016,01-2016,01-2016,02-2016,03-2016,04-2016,05-2016,06-2016,07-2016,08-2016,09-2016,10-2016,11-2016,12-2016

Upvotes: 0

Views: 1409

Answers (4)

BA.
BA.

Reputation: 934

Use the query provided by Ponder Stibbons

select to_char(add_months(date '2016-01-01', level - 1), 'mm-yyyy') mth 
  from dual 
  connect by level <= 24

Then you can join this to your table to get any required stats related to the doctor_checkup. Note that the join should be on the month of the checked_date in your "doctor_checkup". You might want to create a function-based index: to_char(checked_date, 'mm-yyyy')

For example, to get the number of appointments per month

select mth, nvl(count(1), 0) appointments
from (select to_char(add_months(date '2016-01-01', level - 1), 'mm-yyyy') mth 
      from dual 
      connect by level <= 24) mo 
      left outer join doctor_checkup dc on mo.mth = to_char(dc.checked_date, 'mm-yyyy')

Upvotes: 0

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

This query generates 24 "month" values:

select to_char(add_months(date '2016-01-01', level - 1), 'mm-yyyy') mth 
  from dual 
  connect by level <= 24

... and this is basic answer. If You want to find missing months You can minus this query and Yours:

select to_char(add_months(date '2016-01-01', level - 1), 'mm-yyyy') mth 
  from dual connect by level <= 24
minus
select to_char(to_date(checked_date, 'dd-mon-yy'), 'mm-yyyy')
  from doctor_checkup

It's similiar to David Faber's answer, I added add_months and second part to answer this particular question.

Looks like checked_date is varchar type, You should avoid storing dates in such format.

Upvotes: 1

sagi
sagi

Reputation: 40471

Use a derived table or a fixed date table (which is more recommended) , like this:

SELECT DISTINCT NVL(t.month_col,s.month) as month_col
FROM(
    SELECT '01-2016' as month_col FROM DUAL
    UNION ALL
    SELECT '02-2016' as month_col FROM DUAL
    UNION ALL 
    .... // all the dates you want
     ) t
LEFT JOIN YourTable s

Though it seems unnecessary here, if you're not using any extra info from your table, just use the inner query of t alias .

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

This is simple. Obviously you don't even need the table when you simply want to select months from '01-2016' to '12-2017':

select '01-2016' as month from dual
union all
select '02-2016' as month from dual
union all
...
union all
select '12-2017' as month from dual

Upvotes: 0

Related Questions