Re: ytd calculation

From: raja <dextersunil_at_gmail.com>
Date: Thu, 4 Feb 2010 04:32:28 -0800 (PST)
Message-ID: <a4ce8239-d671-4018-aa7a-1cba34550401_at_m24g2000prn.googlegroups.com>

Thanks for your response.
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; Received on Thu Feb 04 2010 - 06:32:28 CST