This discussion is archived
9 Replies Latest reply: Nov 26, 2012 5:35 AM by 963994 RSS

getting last year column value from a single table

963994 Newbie
Currently Being Moderated
I am having the following columns in my table
BRANCH_CD
YYMM
VNDR#
VGROUP#
SALES_TRGT_AMT
SALES_ACTL_AMT
CUM_TRGT_AMT
CUM_ACTL_AMT

i need to get sales_actl_amt from this year and sales_actl_amt from last year from a single table
pls help
thank you

Edited by: 960991 on Nov 19, 2012 11:13 PM
  • 1. Re: getting last year column value from a single table
    only.ashish99 Newbie
    Currently Being Moderated
    Please define your question little more. and check this (as per my understanding)
    select yymm, sum(sales_actl_amt) from table_name
    group by yymm
  • 2. Re: getting last year column value from a single table
    963994 Newbie
    Currently Being Moderated
    Hi ashish,
    first i will thanks to you.
    as per your given query
    select yymm, sum(sales_actl_amt) from table_name
    group by yymm
    it will give present year sales_actl_amt
    but i want last year sales_actl_amt and present year sales_actl_amt.

    thank you
  • 3. Re: getting last year column value from a single table
    only.ashish99 Newbie
    Currently Being Moderated
    CREATE TABLE f AS (SELECT 2012 AS year, 2000 actl_amt FROM dual UNION ALL
                       SELECT 2011 AS year, 3000 actl_amt FROM dual UNION ALL
                           SELECT 2012 AS year, 2500 actl_amt FROM dual); 
                 
     SELECT year,SUM(Actl_amt) FROM f
     GROUP BY year
    
    Year  SUM(Actl_amt)
     2011          3000
     2012          4500
    this will give u last year result if the year is entered. If this is not the answer u want then please let me know the actual result format u want.

    Edited by: only.ashish99 on Nov 20, 2012 12:01 PM
  • 4. Re: getting last year column value from a single table
    only.ashish99 Newbie
    Currently Being Moderated
    CREATE TABLE f AS (SELECT 2012 AS year, 2000 actl_amt FROM dual UNION ALL
                       SELECT 2011 AS year, 3000 actl_amt FROM dual UNION ALL
                           SELECT 2012 AS year, 2500 actl_amt FROM dual); 
                 
     SELECT year,SUM(Actl_amt) FROM f
     GROUP BY year
    
    Year  SUM(Actl_amt)
     2011          3000
     2012          4500
    this will give u last year result if the year is entered. If this is not the answer u want then please let me know the actual result format u want.
  • 5. Re: getting last year column value from a single table
    963994 Newbie
    Currently Being Moderated
    Hi ashish,
    but i can't use unions in my reports.
    once view my query :

    select t.branch_cd,b.branch_e_name,t.vndr#,v.vndr_name,
    sum(nvl(t.sales_actl_amt,0)) sales_actl_amt
    from inv_sales_trgt_val t,branches b,vendor v where
    t.branch_cd=b.branch_cd and
    t.vndr#=v.vndr# and
    (t.yymm between :fiscal_month and :fiscal_month2) and
    (:fiscal_month<>trunc(:fiscal_month2,-2)) and :fiscal_month2<>trunc(:fiscal_month2,-2)) and t.branch_cd between :from_branch and to_branch and
    t.vndr# between :from_vndr and :to_vndr
    group by t.vndr#,v.vndr_name,t.branch_cd,b.branch_e_name
    order by t.vndr#,t.branch_cd;

    how can i get last year sales_actl_amt .
  • 6. Re: getting last year column value from a single table
    only.ashish99 Newbie
    Currently Being Moderated
    use this function on your date parameter and extract only year. based on that you can get every value related to that year. For previous year also.
    extract(YEAR FROM DATE '2003-08-22')       would return 2003
  • 7. Re: getting last year column value from a single table
    963994 Newbie
    Currently Being Moderated
    hi ashsish,
    can you edit my given query and show in which parameter i have to apply extract function.
    thanks a lot.
  • 8. Re: getting last year column value from a single table
    963994 Newbie
    Currently Being Moderated
    Hi ashish,
    can you give me some blogs or website or oracle reports developing .
    thank you.
  • 9. Re: getting last year column value from a single table
    963994 Newbie
    Currently Being Moderated
    Hi ashish,
    I got the answer

    select t.branch_cd,b.branch_e_name,t.vndr#,v.vndr_name,
    sum(nvl(t.sales_actl_amt,0)) sales_actl_amt
    from inv_sales_trgt_val t,branches b,vendor v where
    t.branch_cd=b.branch_cd and
    t.vndr#=v.vndr# and
    (t.yymm between (:fiscal_month-100) and (:fiscal_month2-100)) and
    (:fiscal_monthtrunc(:fiscal_month2,-2)) and :fiscal_month2trunc(:fiscal_month2,-2)) and t.branch_cd between :from_branch and to_branch and
    t.vndr# between :from_vndr and :to_vndr
    group by t.vndr#,v.vndr_name,t.branch_cd,b.branch_e_name
    order by t.vndr#,t.branch_cd;

    thanks for your reply's.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points