CODEWITHSUNDEEP
sqloracle-database
joe
joe

Reputation: 173

How to include all months of a year in query

I have a table :

ID1  ID2  ID3    DATE     VALUE
  1   1    1     APR-18    5

I want to get the rows for the missing months : Suppose I am considering the year 2018 , so my output should look like this :

ID1    ID2  ID3    DATE     VALUE
 1      1    1     JAN-18    0
 1      1    1     FEB-18    0
 1      1    1     MAR-18    0
 1      1    1     APR-18    5
 1      1    1     MAY-18    0
 1      1    1     JUN-18    0
 1      1    1     JUL-18    0
 1      1    1     AUG-18    0
 1      1    1     SEP-18    0
 1      1    1     OCT-18    0
 1      1    1     NOV-18    0
 1      1    1     DEC-18    0

I tried cross join with this :

select add_months(date '2018-01-01', level - 1) as month
  from dual
  connect by level <=12

but this does not give the intended result . Please help

Upvotes: 1

Views: 196

Answers (2)

APC
APC

Reputation: 146229

Your subquery returns the months of the year correctly. You haven't posted your whole query so not sure why you say it doesn't work. Anyway, here's how to finagle a cross join to get the results you want:

with cal as (
  select add_months(date '2018-01-01', level - 1) as month
  from dual
  connect by level <=12
  )
select t23.id1
       , t23.id2
       , t23.id3
       , to_char(cal.month, 'MON-YY') as dt
       , case when to_char(cal.month, 'MON-YY') = t23.dt
          then t23.val
          else 0 end as val
from cal
     cross join t23
order by cal.month;

And a SQL Fiddle demo to prove it works.

Upvotes: 2

vivek kumar
vivek kumar

Reputation: 63

Please use this to get correct answer

    WITH DateYear AS
    (
    SELECT 0 AS num
    UNION ALL 
    SELECT num + 1 FROM DateYear 
    WHERE num < 11
    )
    select id1,id2,id3,
    case when dd.date1 = dt then dt else '0' end
    dt,
    case when dd.date1 = dt then val else '0' end
    val from (
    SELECT CONVERT(DATE,DATEADD(MONTH,num,CAST(YEAR(GETDATE()) AS vARCHAR))) AS Date2
    ,  Upper(CONVERT(CHAR(3), CONVERT(DATE,DATEADD(MONTH,num,CAST(YEAR(GETDATE()) AS vARCHAR))), 100))+'-'+ Right               (CONVERT(CHAR(4), CONVERT(DATE,DATEADD(MONTH,num,CAST(YEAR(GETDATE()) AS vARCHAR))), 120) ,2) date1
     from DateYear ) dd 
     cross join t23

Upvotes: -1

Related Questions