Home » SQL & PL/SQL » SQL & PL/SQL » YTD Calculation using analytical functions (Oracle Database 10gR1)
YTD Calculation using analytical functions [message #441971] Thu, 04 February 2010 03:09 Go to next message
tssr_2001
Messages: 8
Registered: August 2006
Junior Member
Hi,

I am using Oracle Database 10gR1.
Please help me to write the following query.

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

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 i do this ytd using any analytical functions (i.e., i have to sum up for every row) ?

Please help.

Thanks.

With Regards,
Raja.
Re: YTD Calculation using analytical functions [message #441973 is a reply to message #441971] Thu, 04 February 2010 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You've got an answer on Usenet forums where it has been asked to you to post a test case.
Same thing here: Post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: YTD Calculation using analytical functions [message #442015 is a reply to message #441971] Thu, 04 February 2010 06:51 Go to previous messageGo to next message
tssr_2001
Messages: 8
Registered: August 2006
Junior Member
Test Case Details :
I have given the DDL and DML statements to work on this scenario.

CREATE TABLE ID_OWN_PS.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;





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 );

[Updated on: Thu, 04 February 2010 06:52]

Report message to a moderator

Re: YTD Calculation using analytical functions [message #442018 is a reply to message #442015] Thu, 04 February 2010 07:00 Go to previous messageGo to next message
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
Junior Member
tssr_2001 wrote on Thu, 04 February 2010 18:21
Test Case Details :
I have given the DDL and DML statements to work on this scenario.
CREATE TABLE ID_OWN_PS.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;

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 ); 

[Updated on: Thu, 04 February 2010 07:02] by Moderator

Report message to a moderator

Re: YTD Calculation using analytical functions [message #442019 is a reply to message #442018] Thu, 04 February 2010 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Could you simplify the test case and post ONLY the relevant columns.
Could you tell us what is the partitioning key?
From your example it seems it is (GL, Curr) but to which table columns correspond these result columns?

Regards
Michel


[Updated on: Thu, 04 February 2010 07:05]

Report message to a moderator

Re: YTD Calculation using analytical functions [message #442025 is a reply to message #442019] Thu, 04 February 2010 07:58 Go to previous messageGo to next message
tssr_2001
Messages: 8
Registered: August 2006
Junior Member
I have given only the relevant columns.
Please consider that there are no indexes or partitions.
Re: YTD Calculation using analytical functions [message #442027 is a reply to message #442025] Thu, 04 February 2010 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have given only the relevant columns.

The results you posted contains only 5 or 6 columns and only 4 comes from the table (and 1 or 2 calculated) so you don't need to post a table with 14 columns).
Your results mention columns GL, Curr and Amount but you didn't say to which table columns these are related to.

Quote:
Please consider that there are no indexes or partitions

When I talk about partition it is logical partition that is on what we have to group by (or partition) to sum?

Regards
Michel
Re: YTD Calculation using analytical functions [message #442072 is a reply to message #442027] Thu, 04 February 2010 11:00 Go to previous messageGo to next message
tssr_2001
Messages: 8
Registered: August 2006
Junior Member
Hope the confusion is rectified in this post.

I have consolidated required data in the upload file.

1. TestCase ( DDL and DML )
2. Query Used ( to get only one summation - for month wise )
3. Output got.

Help Required :
Re-writing query to get double summation - to get year wise
for every record in that table.
  • Attachment: ytd_1.sql
    (Size: 8.89KB, Downloaded 286 times)
Re: YTD Calculation using analytical functions [message #442091 is a reply to message #442072] Thu, 04 February 2010 11:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't simplify the example.
Stop.
We don't need all columns for a test case.
Stop.
Waiting...

Regards
Michel
Re: YTD Calculation using analytical functions [message #442143 is a reply to message #442091] Fri, 05 February 2010 00:11 Go to previous messageGo to next message
tssr_2001
Messages: 8
Registered: August 2006
Junior Member
To simply example, here comes :
I have a table with 4 columns : col1 col2 col3 col4.
col4 is amt column.
eg : ( we have 4 rows here )
col1 col2 col3 col4
101 AB 1501 10.0
101 AB 1501 20.0
101 AB 1501 30.0
101 AB 1501 40.0

Now, i need to get the summation of col4 to calculate month and year wise summation ( so 2 summations have to be done on col4 alone, for every value of all columns ):

mtd - Monthwise ( 1st summation )
----------------------------
10.0 (col4 1st row value, itself)
30.0 (col4 1st row + 2nd row value)
60.0 (col4 1st + 2nd + 3rd row value)
100.0 (col4 1st + 2nd + 3rd + 4th row value)

So we get mtd as follows ( this is first summation ):
col1 col2 col3 col4 mtd
101 AB 1501 10.0 10.0
101 AB 1501 20.0 30.0
101 AB 1501 30.0 60.0
101 AB 1501 40.0 100.0

ytd - Yearwise ( 2nd summation )
----------------------------
10.0 (col4 1st row value, itself)
40.0 (col4 1st row + 2nd row value)
60.0 (col4 1st + 2nd + 3rd row value)
100.0 (col4 1st + 2nd + 3rd + 4th row value)

So we get ytd as follows ( this is second summation, got from 1st summation ): Here col4 is derived values of mtd calculation.
col1 col2 col3 col4 ytd
101 AB 1501 10.0 10.0
101 AB 1501 30.0 40.0
101 AB 1501 60.0 100.0
101 AB 1501 100.0 200.0

so finally we are doing summation ( like incremental summation for a column ) twice for col4 to calculate ytd, by grouping all the other columns ( like col1, col2, col3 )

Is this example fine ?

[Updated on: Fri, 05 February 2010 00:15]

Report message to a moderator

Re: YTD Calculation using analytical functions [message #442155 is a reply to message #442143] Fri, 05 February 2010 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no date column in your table, so month wise and year wise is irrelevant and sum of sum is not the same thing has sum along months and sum along years.
And where is the script I can execute?

Just repost the previous script but with just the columns you called GL, Curr, Day and Amount in your first post.

Regards
Michel
Re: YTD Calculation using analytical functions [message #442181 is a reply to message #441971] Fri, 05 February 2010 03:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can do the MTD figures like this:
with src as (select 5805 GL,  45454 curr, to_date('1-Jan-2010','dd-mon-yyyy') dy, 5.23 amount from dual union all
             select 5805, 45454, to_date(' 2-Jan-2010','dd-mon-yyyy'), -4.52 from dual union all
             select 5805, 45454, to_date(' 3-Jan-2010','dd-mon-yyyy'), 25.3 from dual union all
             select 5805, 45454, to_date(' 4-Jan-2010','dd-mon-yyyy'), 10.53 from dual union all
             select 5805, 45454, to_date(' 5-Jan-2010','dd-mon-yyyy'), -1.88 from dual)
select gl
      ,curr
      ,dy
      ,sum(amount) over (partition by gl,curr,to_char(dy,'MON-YYYY') order by dy) mtd
from   src;


I can't get my head round your YTD calculation - you are adding the same values up again and again.
All you should need to do for a YTD column is to just sum everything for the year up to that point:
select gl
      ,curr
      ,dy
      ,sum(amount) over (partition by gl,curr,to_char(dy,'MON-YYYY') order by dy) mtd
      ,sum(amount) over (partition by gl,curr,to_char(dy,'YYYY') order by dy) ytd
from   src;
icon3.gif  Re: YTD Calculation using analytical functions [message #442192 is a reply to message #442181] Fri, 05 February 2010 04:27 Go to previous messageGo to next message
tssr_2001
Messages: 8
Registered: August 2006
Junior Member
Sorry for messing the way i described my needs.

Good input, i will try out with OVER Clause.

If you have any URL's to learn analytical functions like OVER Clause ( with well explained examples ), Please share it.

If i have any doubts, will get back again. Smile

Thanks for all of your responses.

[Updated on: Fri, 05 February 2010 04:27]

Report message to a moderator

Re: YTD Calculation using analytical functions [message #442193 is a reply to message #442192] Fri, 05 February 2010 04:29 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe the documentation?
Database SQL Reference
Chapter 5 Functions
Section Analytic Functions

And examples in each and every analytic functions.

Regards
Michel
Previous Topic: How to create a Shanpshot (in Oracle 8.0.5) to get data from Oracle 11g
Next Topic: Exception : ORA-06502: PL/SQL: numeric or value error
Goto Forum:
  


Current Time: Sat Dec 03 12:28:41 CST 2016

Total time taken to generate the page: 0.04429 seconds