Archives of the TeradataForum

Message Posted: Mon, 14 Mar 2011 @ 10:53:05 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

I am new to teradata. I have a requirement. There are a master contract which will have more then one account(some times it will have one account). I have written the query(see below). But It is not full fill my requirement.

Requirement :

You only need to get the balance from BALN_FI_ACCT if the Master Contract has transactions in the PAYT_ITEM table. If there are no transactions for any Accounts in the Master Contract then the balance is not required. Therefore once you have all the Master Contracts and Accounts, you should then check PAYT_ITEM for transactions. If you find transactions with a UTC_DTTS_POST between 08:00 and 20:30 AEST for that day, then you will need to extract all transactions for that day. You also need to get the balances for all accounts for that Master Contract.

The balances for all accounts for the Master Contract then need to summed together, which is the starting balance for the day.

Aggregate SNAP Account Balance
Sum all balances for all accounts belonging to the Master Contract.

Aggregate DR SNAP Account Balance
Sum only negative (debit) balances for accounts belonging to the Master Contract.

Aggregate CR SNAP Account Balance
Sum only positive (credit) balances for accounts belonging to the Master Contract.

So a single Master Contract will have one balance for each of the above. If there are no accounts with a Debit balance, then Aggregate DR SNAP Account Balance will be zero. If there are no accounts with a Credit balance, then Aggregate CR SNAP Account Balance will be zero.

You then need to apply the transactions found in PAYT_ITEM in order according to UTC_DTTS_POST. Section 5.1.2.5 describes how to determine whether the transaction for the account is a Debit (-) or a Credit (+). If the transaction is a Debit, the amount will be deducted from the 3 aggregate balances above. If the transaction is a Credit, the amount will be added to the 3 aggregate balances above.

Query:

     SELECT PIC.ACCT, PIC.POST_DATE, utc_dtts_crat,
     COALESCE(AGGR_TRAN_A,0.00 ) + COALESCE (AGGR_TRAN_B ,0.00) AS AGGR_SNAP_BALN_AMT,
            CASE WHEN DR_CR_INDA IS NULL
                  AND REVS_INDA IS NULL
                 THEN -1 * AMT_ACCT_CNCY
                   WHEN DR_CR_INDA = 'X'
                  AND REVS_INDA ='X'
                 THEN  -1 * AMT_ACCT_CNCY
                                   END AS TRAN_A,
                  CASE
                 WHEN DR_CR_INDA = 'X'
                  AND REVS_INDA IS NULL
                 THEN  AMT_ACCT_CNCY
                   WHEN DR_CR_INDA IS NULL
                  AND REVS_INDA ='X'
                 THEN AMT_ACCT_CNCY
                  END AS TRAN_B,

            CAST(SUM(TRAN_A) OVER
            (PARTITION BY IA1.INTR_CNCT_NUMB_OF_MAIN_CNCT,PIC.ACCT,PIC.POST_DATE
                 ORDER BY UTC_DTTS_CRAT,
                          UTC_DTTS_POST,
                          VALU_DATE,
                          PAYT_ITEM_ID,
                          POSN_NUMB_IN_PAYT_ITEM
                          ROWS UNBOUNDED PRECEDING) AS DEC(15,2)) AS AGGR_DR_SNAP_BALN_AMT

                   CAST(SUM(TRAN_B) OVER
            (PARTITION BY  IA1.INTR_CNCT_NUMB_OF_MAIN_CNCT,PIC.ACCT,PIC.POST_DATE
                 ORDER BY UTC_DTTS_CRAT,
                          UTC_DTTS_POST,
                          VALU_DATE,
                          PAYT_ITEM_ID,
                          POSN_NUMB_IN_PAYT_ITEM
                          ROWS UNBOUNDED PRECEDING) AS DEC(15,2)) AS AGGR_CR_SNAP_BALN_AMT


        FROM STvcbods.PAYT_ITEM_CURR PIC
        INNER JOIN
       TVCBDMEXTR.IDCA_ACCT1 IA1
       ON IA1.ACCT=PIC.ACCT
        INNER JOIN
         TvCBODS.BALN_FI_ACCT BFA
        ON IA1.ACCT=BFA.ACCT
        --AND PIC.ACCT='0000646E704C2200E10080000A1F2390'
       and  CAST(UTC_DTTS_CRAT AS TIME(6)) BETWEEN CAST('00:00:01.000000' AS
          TIME(6)) AND CAST('23:59:59.000000' AS TIME(6)

Please let me know if you need any detatails.

Regards

Srinivasu

	<Prev	Next>		<<First	<Prev	Next>	Last>>