Re: ytd calculation

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Fri, 5 Feb 2010 13:43:15 -0800 (PST)
Message-ID: <bff82b0a-6af3-4bc7-9eca-f62c79636f1b_at_u9g2000yqb.googlegroups.com>

On Feb 4, 12:08 pm, raja <dextersu..._at_gmail.com> wrote:
> Thanks for your immediate reply.
>
> I tried to do summation for month-wise ( using over clause - tried
> example query, is present in the above posts )
> But, i dont know whether that is correct or not !!!
>
> Now, i have to try another summation of the same type ; to get year-
> wise summation, with the data acquired from month wise summation,
> So, i want to rewrite a single query, so that, i get double summation
> for a column grouped by all the other columns.
>
> Can that be done !!!! :-)
>
> Hope you understand my need.
>
> Thanks.

I got busy and have not been able to return to this but I will post what I have in case it might be of help. You have more columns in the transaction table that in your intiail post and you do not identify them. Normally when you summ a value, say sales, for a date, MTD, and YTD you do it by product or general ledger account. I cannot tell from your post what you actually want and your sample data is all the same acct, date, etc so I made a couple of changes to have more than one data and more than one gl acct.

SQL> select GL_POST_ACCN_COD, trunc(CURR_ACCN_PERIOD_START_DT),   2 sum(FUNCT_AMNT_FAA_NB) DAY
  3 from transaction
  4 group by GL_POST_ACCN_COD, trunc(CURR_ACCN_PERIOD_START_DT)   5 /

GL_POST_ACCN_COD TRUNC(CUR DAY

---------------- --------- ----------
          474093 27-FEB-06       7.47
          474094 20-FEB-06       7.41
          474094 25-FEB-06        .51
          474094 27-FEB-06      17.15
          474094 27-MAR-06      42.83
          474094 27-FEB-07      13.91
          474094 27-FEB-08      43.53

7 rows selected.

SQL>
SQL> select GL_POST_ACCN_COD, trunc(CURR_ACCN_PERIOD_START_DT,'MM'),   2 sum(FUNCT_AMNT_FAA_NB) MTD
  3 from transaction
  4 group by GL_POST_ACCN_COD, trunc(CURR_ACCN_PERIOD_START_DT,'MM')   5 /

GL_POST_ACCN_COD TRUNC(CUR MTD

---------------- --------- ----------
          474093 01-FEB-06       7.47
          474094 01-FEB-06      25.07
          474094 01-MAR-06      42.83
          474094 01-FEB-07      13.91
          474094 01-FEB-08      43.53

SQL>
SQL> select GL_POST_ACCN_COD, trunc(CURR_ACCN_PERIOD_START_DT,'YYYY'),   2 sum(FUNCT_AMNT_FAA_NB) YTD
  3 from transaction
  4 group by GL_POST_ACCN_COD,
trunc(CURR_ACCN_PERIOD_START_DT,'YYYY')
  5 /

GL_POST_ACCN_COD TRUNC(CUR YTD

---------------- --------- ----------
          474093 01-JAN-06       7.47
          474094 01-JAN-06       67.9
          474094 01-JAN-07      13.91
          474094 01-JAN-08      43.53

If you placed each of these queries in the FROM clause as an inline view and joined the results together on the gl_post_accn_cod column you would have day, MTD, and YTD figures.

Like I said I changed the data but the idea should be clear since no one else has posted a more eligant solution I went ahead and posted this.

HTH -- Mark D Powell -- Received on Fri Feb 05 2010 - 15:43:15 CST