ytd calculation
raja
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.
Mark D Powell
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 --
raja
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.
Thomas Kellerer
> 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
raja
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...
gazzag
>
> Please help...
If you want people to help, why not do as Mark D Powell said in
response to your OP?
1. Post the DDL to create the table.
2. Post the DML to populate the table with some test data.
You're asking us to do a lot of work on your behalf otherwise.
HTH
-g
raja
Sorry missed the ddl and dml.
DDL and DML are as follows :
CREATE TABLE TRANSACTION
(
CURRENCY_FUNC_COD NUMBER(12) NOT NULL,
DATA_TYPE_COD NUMBER(12) NOT NULL,
GL_POST_ACCN_COD NUMBER(12) NOT NULL,
PRDMKT_COD NUMBER(12) NOT NULL,
SCENARIO_COD NUMBER(12) NOT NULL,
ACCN_PERIOD_COD NUMBER(12) NOT NULL,
TRANS_DES_VC VARCHAR2(300 BYTE),
CURRENCY_TRAN_COD NUMBER(12) NOT NULL,
REP_ENT_COD NUMBER(12) NOT NULL,
COST_CENTER_COD NUMBER(12) NOT NULL,
ACCN_PERIOD_COD_1 NUMBER(12) NOT NULL,
ACCN_YEAR_COD_VC VARCHAR2(100 BYTE) NOT NULL,
CURR_ACCN_PERIOD_START_DT DATE,
FUNCT_AMNT_FAA_NB NUMBER
);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
7.47);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
0.51);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
0.51);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
43.53);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
47.79);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
0.51);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1.58);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
-43.53);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
7.41);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
42.32);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
2.44);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
13.91);
Insert into ID_OWN_DW.ID_T_DW_MST_TIME_PER2FY
(CURRENCY_FUNC_COD, DATA_TYPE_COD, GL_POST_ACCN_COD, PRDMKT_COD,
SCENARIO_COD, ACCN_PERIOD_COD, TRANS_DES_VC, CURRENCY_TRAN_COD,
REP_ENT_COD, COST_CENTER_COD, ACCN_PERIOD_COD_1, ACCN_YEAR_COD_VC,
CURR_ACCN_PERIOD_START_DT, FUNCT_AMNT_FAA_NB)
Values
(525, 1, 474094, 153383, 1, 943, 'AMEX', 525, 66013, 140103, 943,
'2006', TO_DATE('02/27/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
8.36);
COMMIT;
Mark D Powell
You realize that all the insert statements are all wrong. You failed
to match the insert table to the sample table_name.
Easy fix. I do not know how much if any time I will get today but I
will try to give this a shot for you. Someone will probably beat me
to a solution.
raja
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.
Mark D Powell
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.