Product date salesp1 20/Jan/1999 100p1 20/Feb/1999 200p1 20/apl/1999 100p1 20/DEC/1999 50p1 20/Jan/2000 100p1 20/mar/2000 500now i want query like product ,sum of actual year ,sum of business yearp1 450 750here actual year means 1 Jan 1999 to 31 st Jan 1999business year means 1 st APR 1999 to 31 st mar 200pls give me answer as early as possible..

Interview Candidate
Mar 30th, 2006
3
1591

Showing Answers 1 - 3 of 3 Answers

sar

Apr 22nd, 2007

If you just need the query.
select product,
sum(case when date > 'xxxx' and date <= 'xxxx' then sales else 0) actual_year,
sum(case when date > 'xxxx' and date <= 'xxxx' then sales else 0) business_year
from
x,y,z
where
xyz
group by product.

In designer you will create fisical year and calender year tables for this purpose and join to the sales table.