| Date Increments [message #9037] |
Tue, 14 October 2003 15:37  |
deay0927
Messages: 2
Registered: October 2003
|
Junior Member |
|
|
Hello
I need to write a procedure that spans 3 years
giving a totalsum and totalcount of donations
per month:
example:
select sum(donation),count(donation)
from gift
where giftdate between '01-JAN-2003' and '31-JAN-2003';
next would be February 1,2003 thru February 28,2003...
and so on...
question:
how to go about incrementing each month by 1 in a
FOR...LOOP taking into account those months that
have 30, 31, 28 days (including leap year).
thanks in advance
|
|
|
|
| Re: Date Increments [message #9038 is a reply to message #9037] |
Tue, 14 October 2003 16:00  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You can do it all in a single query - no loop needed. You just group by the month (converted all dates to the first of that month):
sql>select to_char(trunc(created, 'mm'), 'FMMonth yyyy') month, count(*)
2 from all_objects
3 group by trunc(created, 'mm');
MONTH COUNT(*)
-------------- ---------
May 2003 23490
June 2003 331
July 2003 21
August 2003 28
September 2003 27
October 2003 121
6 rows selected.
Your query would be something like:
select trunc(giftdate, 'mm') month, sum(donation), count(donation)
from gift
where giftdate between :start_date and :end_date
group by trunc(giftdate, 'mm'); |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
