---------------Now the requirement is-----------------
In first row it should show the value of sales for one month say 01-aug-03.
In second row it should show the value of sales cumulative i.e from 01-apr-02 to 01-mar-03.(cum_02)
In second row it should show the value of sales cumulative i.e from 01-apr-03 to 01-aug-03.(cum_03)
I am able to produce , what is required in first row and third row the problem is coming for 2nd row i.e cum_02 only.
I have written following query
select salesman,
product,sum(decode(to_char(sal_month),'01-AUG-03',wss_qty,0))
aug_03,
sum(wss_qty) cum_03
from mssav_wd_sales_summary
where sal_month between '01-apr-03' and '01-sep-03'
group by salesman,product
/
it will give me aug_03 and cum_03 figures but not cum_02.If i change the where condition as "where sal_month between '01-apr-02' and '01-aug-03'"then the whole outut will be wrong because cum_03 will start including the figures from 01-apr-02 , but i want it to include period from 01-apr-03 onwards only.
So please help me in designing the query.
Then i will reuqest for the second part of my problem.
Regards,
Rajeev Katyal
Your WHERE clause requires to meet two (2) condition
(1) for cum_02 date range is 01-apr-02 to 01-mar-03
(2) for cum_03 date range is 01-apr-03 to 01-aug-03
because of this reason, you should use SET OPERATORS like UNION, or UNION ALL. query would be as follow:
select salesman,
product,sum(decode(to_char(sal_month),'01-AUG-03',wss_qty,0))
aug_03, 0 cum_2,
sum(wss_qty) cum_03,
0 cum_02
from mssav_wd_sales_summary
where sal_month between '01-apr-03' and '01-sep-03'
group by salesman,product
UNION -- set operator
/* add new query for cum_02 */
select salesman,
product, 0 aug_03, sum(decode(to_char(sal_month),'01-APR-03',wss_qty,0))
apr_02,
0 cum_03,
sum(wss_qty) cum_02
from mssav_wd_sales_summary
where sal_month between '01-apr-03' and '01-sep-03'
group by salesman,product
/
that you mean?