#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    1
    Rep Power
    0

    decode - designing query - please help


    I am not able to write one query.....actually the problem is quite big..but for the sake of clarity i will putting the same in parts.

    consider the following table with below mentioned colums.

    MSSA_SALES
    --------------------
    salesman
    product
    quantity
    sal_month
    --------------------

    The table contains rows of sales figures for different salesman/product/month.

    Like

    salesman product quantity sal_month
    ----------------------------------------------------------
    john Butter 10 01-apr-02
    Smith Cheese 20 01-apr-02
    John Cheese 1 01-apr-02

    john Butter 10 01-dec-02
    Smith Cheese 20 01-dec-02
    John Cheese 1 01-dec-02

    john Butter 10 01-apr-03
    Smith Cheese 20 01-apr-03
    John Cheese 1 01-apr-03
    paul powder 53 01-aug-03
    adams chocolate 43 01-aug-03

    now i want the output in following format.

    Butter Cheese Powder chocolate
    John
    aug_03 20 34 56 0
    Cum_02 100 20 23 24
    Cum_03 5 4 5 0

    Smith
    aug_03 23 44 0 34
    Cum_02 11 11 11 11
    Cum_03 1 2 3 4


    ---------------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
  2. #2
  3. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    12

    decode - designing query


    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?

IMN logo majestic logo threadwatch logo seochat tools logo