Prev: Optimizer issue in 11g
Next: New dbTrends Software Product for Oracle AWR and STATSPACK Released
From: raja on 3 Feb 2010 06:19
Hi,

If, I want to calculate ytd ( year to date calculation ), then i have
to sum up monthly then sum up for yearly.
Eg : I have to calculate YTD as follows :

GL Curr Day Amount Month YTD
5805 45454 1-Jan 5.23 5.23 5.23
5805 45454 2-Jan -4.52 0.71 5.94
5805 45454 3-Jan 25.3 26.01 31.95
5805 45454 4-Jan 10.53 36.54 68.49
5805 45454 5-Jan -1.88 34.66 103.15

how can i do this ytd using rollup or group by clauses (i.e., i have
to sum up for every row) ?

Thanks.

With Regards,
Raja.
From: Mark D Powell on 3 Feb 2010 08:49
On Feb 3, 6:19 am, raja <dextersu...(a)gmail.com> wrote:
> Hi,
>
> If, I want to calculate ytd ( year to date calculation ), then i have
> to sum up monthly then sum up for yearly.
> Eg : I have to calculate YTD as follows :
>
> GL Curr Day Amount Month YTD
> 5805 45454 1-Jan 5.23 5.23 5.23
> 5805 45454 2-Jan -4.52 0.71 5.94
> 5805 45454 3-Jan 25.3 26.01 31.95
> 5805 45454 4-Jan 10.53 36.54 68.49
> 5805 45454 5-Jan -1.88 34.66 103.15
>
> how can i do this ytd using rollup or group by clauses (i.e., i have
> to sum up for every row) ?
>
> Thanks.
>
> With Regards,
> Raja.

How about posting a the create table DDL with insert statements for
the data. It would save a lot of time. However, what you want is
unclear in that looking at your sample data you have what appear to be
current, month, and YTD data columns so all you would need is to sum
all three columns. A better explanation might also save someone from
producing the wrong solution to what you really need.

HTH -- Mark D Powell --

From: raja on 3 Feb 2010 09: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 )

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.
From: Thomas Kellerer on 3 Feb 2010 09:22
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
From: raja on 4 Feb 2010 06:12
I have tried an example.

1. is this correct for month summation alone ?
2. should i again do the same procedure to get for year summation
too ?
i.e., take the below query data ( month summation ) as input and again
form the same query for year summation ?

SELECT
A.CURR,
A.DATA_TYPE_COD,
A.GL_POST_ACCN,
A.PRDMKT_COD,
A.SCENARIO_COD,
A.ACCN_PERIOD_COD,
A.TRANS_DES,
A.CURRENCY_TRAN_COD,
A.REP_ENT_COD,
A.COST_CENTER,
B.ACCN_PERIOD_COD,
B.ACCN_YEAR,
B.CURR_ACCN_START_DT,
A.FUNC_AMNT,
SUM(A.FUNC_AMNT) MONTH
OVER
(
ORDER BY
A.CURR,
A.DATA_TYPE_COD,
A.GL_POST_ACCN,
A.PRDMKT_COD,
A.SCENARIO_COD,
A.ACCN_PERIOD_COD,
A.TRANS_DES,
A.CURRENCY_TRAN_COD,
A.REP_ENT_COD,
A.COST_CENTER,
B.ACCN_PERIOD_COD,
B.ACCN_YEAR,
B.CURR_ACCN_START_DT,
A.FUNC_AMNT
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS "YTD"
FROM
MASTER B,
TRANSACTION A
WHERE ( A.ACCN_PERIOD_COD = B.ACCN_PERIOD_COD );

Please help...
 |  Next  |  Last
Pages: 1 2
Prev: Optimizer issue in 11g
Next: New dbTrends Software Product for Oracle AWR and STATSPACK Released