Archives of the TeradataForum
Message Posted: Mon, 14 Mar 2011 @ 10:53:05 GMT
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.
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
Aggregate DR SNAP Account Balance
Aggregate CR SNAP Account Balance
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 18.104.22.168 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.
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|