DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Report by month

  1. #1

    Question 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?

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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!!
    Assistance is Futile...

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool


    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;


    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    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.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Populate it with months for the next hundred years.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width