Re: ytd calculation

From: raja <dextersunil_at_gmail.com>
Date: Wed, 3 Feb 2010 06:12:45 -0800 (PST)
Message-ID: <be42589c-07a4-4268-8b49-d6c341cfb048_at_u15g2000prd.googlegroups.com>

Here in this example, we have column names as gl, curr, day, amount, month, year ; taken from a 1 transaction table.

Now, To calculate ytd (year to-date calculation), i have to calculate month first and then year.

Step 1: Month wise summation :
I have to calculate sum for each day of the month.

From above example for month wise summation: GL Curr Day Amount Month
5805 45454 1-Jan 5.23 5.23 ( sum of jan1 = 5.23 ) 5805 45454 2-Jan -4.52 0.71 ( sum of jan2 = 5.23 + (-4.52) = 0.71 )
5805 45454 3-Jan 25.3 26.01 ( sum of jan3 = 0.71 + 25.3 = 26.01 ) 5805 45454 4-Jan 10.53 36.54 ( sum of jan4 = 26.01 + 10.53 = 36.54 )
5805 45454 5-Jan -1.88 34.66 ( sum of jan4 = 36.54 + (-1.88) = 34.66 )

Step 2: Year wise summation : YTD Calculation : We have done Step1 process to calculate this Step2 process, i.e., YTD Calculation.
So, we have to do Year wise summation with the Step1 month wise data ( with the above output date ).

Again, from above example for year wise summation: GL Curr Day Amount Month YTD

5805 45454 1-Jan 5.23    5.23   5.23    ( ytd = 5.23 )
5805 45454 2-Jan -4.52   0.71   5.94    ( ytd = 5.23 + 0.71 = 5.94)
5805 45454 3-Jan 25.3    26.01  31.95   ( ytd = 5.94 + 26.01 =

31.95 )
5805 45454 4-Jan 10.53 36.54 68.49 ( ytd = 31.95 + 36.54 = 68.49 )
5805 45454 5-Jan -1.88 34.66 103.15 ( ytd = 68.49 + 34.66 = 103.15 )

So for year to-date calculation, we have to sum all the dates for a month and then sum all the month to get ytd.

How can we achieve this using group by / rollup ???

Please help. Received on Wed Feb 03 2010 - 08:12:45 CST