9 Replies Latest reply: Nov 26, 2012 7:35 AM by 963994 RSS

    getting last year column value from a single table

    963994
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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.