Report by month

I have a table of transactions with a date column. I am trying to make a report of the number for each month. There aren't transactions for every month so I need to be able to show 0 for those months.

Normally I'd do a select count(*), date from FOO group by date to get the data. However this leaves out the months with no data.

One suggestion I got was to create a dummy table w/several hundred rows, then create a view that does a add_months against the ROWNUM pseudocolumn, then do a outer join to that view. That works but it feels like the wrong way to me. Is there a better way (outside of a cursor) to do this?

I'd recommend that you create a table of months for this. You can do various other techniques, such as generating the rows with CONNECT BY and whatnot but you really can't beat having actual data to select -- the optimizer will thank you ;)

I wrote this for a similar purpose, you may be able to mess about with it to get what you are after...

Code:

---

select EXP_DATE,
          JANUARY,
          FEBRUARY,
          MARCH,
          APRIL,
          MAY,
          JUNE,
          JULY,
          AUGUST,
          SEPTEMBER,
          OCTOBER,
          NOVEMBER,
          DECEMBER from (
select EXP_DATE,
        first_value(JANUARY) over (partition by EXP_DATE order by EXP_DATE) JANUARY,
        first_value(FEBRUARY) over (partition by EXP_DATE order by EXP_DATE) FEBRUARY,
        first_value(MARCH) over (partition by EXP_DATE order by EXP_DATE) MARCH,
        first_value(APRIL) over (partition by EXP_DATE order by EXP_DATE) APRIL,
        first_value(MAY) over (partition by EXP_DATE order by EXP_DATE) MAY,
        first_value(JUNE) over (partition by EXP_DATE order by EXP_DATE) JUNE,
        first_value(JULY) over (partition by EXP_DATE order by EXP_DATE) JULY,
        first_value(AUGUST) over (partition by EXP_DATE order by EXP_DATE) AUGUST,
        first_value(SEPTEMBER) over (partition by EXP_DATE order by EXP_DATE) SEPTEMBER,
        first_value(OCTOBER) over (partition by EXP_DATE order by EXP_DATE) OCTOBER,
        first_value(NOVEMBER) over (partition by EXP_DATE order by EXP_DATE) NOVEMBER,
        first_value(DECEMBER) over (partition by EXP_DATE order by EXP_DATE) DECEMBER,
        row_number() over (partition by EXP_DATE order by EXP_DATE) RN
from (
SELECT        to_char(expiry_date, 'DD') EXP_DATE,
        decode(to_char(expiry_date, 'MON'),'JAN', cnt, null) JANUARY,
        decode(to_char(expiry_date, 'MON'),'FEB', cnt, null) FEBRUARY,
        decode(to_char(expiry_date, 'MON'),'MAR', cnt, null) MARCH,
        decode(to_char(expiry_date, 'MON'),'APR', cnt, null) APRIL,
        decode(to_char(expiry_date, 'MON'),'MAY', cnt, null) MAY,
        decode(to_char(expiry_date, 'MON'),'JUN', cnt, null) JUNE,
        decode(to_char(expiry_date, 'MON'),'JUL', cnt, null) JULY,
        decode(to_char(expiry_date, 'MON'),'AUG', cnt, null) AUGUST,
        decode(to_char(expiry_date, 'MON'),'SEP', cnt, null) SEPTEMBER,
        decode(to_char(expiry_date, 'MON'),'OCT', cnt, null) OCTOBER,
        decode(to_char(expiry_date, 'MON'),'NOV', cnt, null) NOVEMBER,
        decode(to_char(expiry_date, 'MON'),'DEC', cnt, null) DECEMBER
FROM  ( select expiry_date,
                to_char(expiry_date, 'DD'),
                to_char(expiry_date, 'MON'),
                count(expiry_date) cnt
        FROM TABLE_X
        group by to_char(expiry_date, 'DD'),
                to_char(expiry_date, 'MON') )
WHERE expiry_date between (Trunc(Sysdate) - 963)
                and (Trunc(Sysdate) - 598)
))
where RN=1;

---

WARNING: It runs like a dog, so execute it on a test system if possible!!

Or, you can generate the months kinda like this:

Code:

---

Select Exp_Mth
    , Sum(Decode(Mth,'Jan',Cnt,0)) Jan
    , Sum(Decode(Mth,'Feb',Cnt,0)) Feb
    , Sum(Decode(Mth,'Mar',Cnt,0)) Mar
    , Sum(Decode(Mth,'Apr',Cnt,0)) Apr
    , Sum(Decode(Mth,'May',Cnt,0)) May
    , Sum(Decode(Mth,'Jun',Cnt,0)) Jun
    , Sum(Decode(Mth,'Jul',Cnt,0)) Jul
    , Sum(Decode(Mth,'Aug',Cnt,0)) Aug
    , Sum(Decode(Mth,'Sep',Cnt,0)) Sep
    , Sum(Decode(Mth,'Oct',Cnt,0)) Oct
    , Sum(Decode(Mth,'Nov',Cnt,0)) Nov
    , Sum(Decode(Mth,'Dec',Cnt,0)) Dec
  From (Select Mth From (Select Level Mth From Dual Connect By Level < 13)) M
    , (
Select To_Char(Expiry_Date, 'Mon') Exp_Mth
    , Count(Expiry_Date) Cnt
  From Table_X
 Where Expiry_Date Between (Trunc(Sysdate) - 963)
                      And (Trunc(Sysdate) - 598)
 Group By To_Char(Expiry_Date, 'Dd')
        , To_Char(Expiry_Date, 'Mon')) D
 Where D.Exp_Mth(+) = M.Mth;

---

:D

Thank you for the replies, I should add that when I say 'month', I mean a listing of month/year such as :

1/1996
2/1996
.
.
.
12/2016

The window of dates will slide as time goes on, so I didn't want to build a table of these and have to add on to it every so often to keep it up to date. Thats the kind of thing that is guarenteed to get forgotten someday.

Populate it with months for the next hundred years.

A real table and its statistics will help the CBO to compute optimum execution plan.

I would opt for a real table, if I were in your position.

Tamil