Re: ytd calculation

From: Thomas Kellerer <OTPXDAJCSJVU_at_spammotel.com>
Date: Wed, 03 Feb 2010 15:22:58 +0100
Message-ID: <7stf63Fnv1U1_at_mid.individual.net>

raja, 03.02.2010 15:12:
> 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 )

Something like:

SELECT gl,

        curr,
        day,
        amount,
        month,
        sum(amount) over (order by day)as ytd

FROM your_table_with_no_name

Thomas Received on Wed Feb 03 2010 - 08:22:58 CST