Year to Date totals [message #2883] |
Mon, 19 August 2002 11:46 |
ED Jessup
Messages: 4 Registered: February 2002
|
Junior Member |
|
|
Can anyone guide me in the right direction to creating a YTD calculation? I what to compare sales for a time period this year to the same equivalent time period last year.
Your help would be appreciated!!
|
|
|
Re: Year to Date totals [message #2884 is a reply to message #2883] |
Mon, 19 August 2002 12:00 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
It's hard to give any specific advice since you did not provide your table structure(s) or any sample data, but in general, you could just group by year to see that results and filter the dates so that data in the previous year that is after the current month/day is excluded.
sql>select *
2 from sales
3 order by sale_date;
SALE_DATE AMOUNT
--------- ---------
01-MAR-01 100
10-APR-01 120
27-MAY-01 10
01-DEC-01 1000
22-JUL-02 50
01-AUG-02 25
15-AUG-02 20
7 rows selected.
sql>select to_char(sale_date, 'yyyy') year, sum(amount) ytd_amount
2 from sales
3 where sale_date between trunc(add_months(sysdate, -12), 'y') and add_months(sysdate, -12)
4 or sale_date between trunc(sysdate, 'y') and sysdate
5 group by to_char(sale_date, 'yyyy');
YEAR YTD_AMOUNT
---- ----------
2001 230
2002 95
|
|
|