SQL to summarize data by month 1

[goodkarma]

Hello,
I'm trying to write a procedure that inserts into a table the total hours worked by dept and area summed by months using YYYY-MM as the date format. The date field I'm looking at is in YYYY-MM-DD format. Currently I'm using AND A.L_DATE_UNDER_RPT >= SYSDATE - 60 and A.L_DATE_UNDER_RPT <= sysdate - 30 but I want to take data that is 2 months old and summarize that every month. So like for May 1st I want to summarize data from March. Is there a way to do this in an Oracle procedure? I don't want to just use the above code because I won't get all the data when there are 31 days in the month. Any help would be GREATLY appreciated.
Thanks

 

[carp]

Something like:

  select to_char(A.L_DATE_UNDER_RPT ,'YYYY-MM'), count(*)
  from my_table a
  where to_char(A.L_DATE_UNDER_RPT,'MM') 
        = to_char(add_months(sysdate,-2),'MM')
 group by to_char(A.L_DATE_UNDER_RPT ,'YYYY-MM');

might do it.

Elbert, CO
1653 MDT

 

[goodkarma]

Thank you!!
That helped a LOT.. But it selects the right month now but it also select month and year of 2 years ago. So it will select 3/2004 and 03/2002 any suggestions as to how I can get to work correctly expecially when looking like in Feb. when you have to in Feb 2005 look at Dec. 2004?/ Any idea's. I have it working where it only gets 2004 data using this...
where (to_char(A.L_DATE_UNDER_RPT,'MM') = to_char(add_months(sysdate,-2),'MM')
and to_char(a.l_date_under_rpt, 'YYYY') = to_char(sysdate, 'YYYY'))
THis is going into a procedure that will run on the first of the month automatically every month.. So I can't have a date be in as a prompt.

 

[carp]

If you need the year factored in, just add it!

select to_char(A.L_DATE_UNDER_RPT ,'YYYY-MM'), count(*)
  from my_table a
  where to_char(A.L_DATE_UNDER_RPT,'YYYY-MM') 
        = to_char(add_months(sysdate,-2),'YYYY-MM')
 group by to_char(A.L_DATE_UNDER_RPT ,'YYYY-MM');

Elbert, CO
1323 MDT

 

[goodkarma]

Thanks a BUNCH!!! Worked like a charm.