...
WHERE CREATED_ON <DATEADD(DAY,-6,CAST (GETDATE() AS DATE))
QUERY TO CHECK FOR DUPLICATE SETTLEMENT
select * from bulk_settlement_records b where transaction_time_end in
(select transaction_time_end from bulk_settlement_records a where BULK_SETTLEMENT_KEY IN('221001200000a3402210041013260013',
'22100207000023242210041014460034',
'221002090000f8f52210041014530038',
'22100210000055cc2210041014580040',
'2210021100009a4f2210041015090042',
'22100212000026932210041015200044',
'221002130000fc9d2210041015320046',
'221002140000a8e42210041015420048',
'221002150000d4472210041015520050',
'221002160000ee8b2210041016070052',
'221002170000bab22210041016330054',
'221002180000f7352210041016450056',
'221002190000221c2210041016560058',
'221003090000fe2f2210041017190087',
'22100310000093242210041017200088',
'221003110000cb1d2210041017320090',
'221003120000c0692210041017470092',
'221003130000a2c12210041018010094',
'22100314000048162210041018120096',
'22100315000003a92210041018320098',
'221003160000c1c32210041018450100',
'22100317000096112210041018590102',
'221003190000f4a22210041019220106',
'2210032100001d602210041019370110',
'2210040700007f1f2210041019460131',
'221004080000312a2210041019520132')
)
and transaction_time_end<=transaction_time_end and transaction_time_begin>=
dateadd(hour,-1,transaction_time_begin)
and transaction_count>31/** THIS LINE SHOULD BE INCLUDED TO REMOVE TRANSACTIONS MIGRATED FROM THE SAME TIME PERIOD BUT ON ANOTHER DAY**/
--and cast (created_at as date) =cast(transaction_time_begin as date)
order by
transaction_time_begin
QUERY TO CHECK FOR TRANSACTION STATUS AND CORRECT MASKED PAN FROM BANK_TRANSFER_TRANSACTION
SELECT
a.[request_time],
a.[response_time],
a.[masked_pan],
a.[stan],
a.[message_name],
a.[response_code],
cast(a.[request_amount] as money)/100 as request_amount,
a.[retrieval_reference_number],
a.[card_acceptor_id],
a.[terminal_id],
a.[card_acceptor_location],
a.[mti],
a.[transaction_search_key]
FROM
[aptent_db].[dbo].[transaction_record] a
INNER JOIN
[aptent_db].[dbo].[bank_transfer_transaction] b
ON
a.[id] = b.[transaction_record_id]
WHERE
concat(a.[terminal_id],a.[retrieval_reference_number]) IN ('2TFD0UU6000172503150')