I am trying to recon two transactional tables.
Table A is the bank report
Table B is the internal report.
Both tables have a matching primary(external_reference_id) and foreign Key (externaltransactionid)
The query should check if the ID exists in both tables, and if the ID’s do match then do the amounts match as well.
If the tbl_dt is null in either it would mean that record is blank.
The problem I have is that my amounts match, but the query says mismatch.
Debit amount and appamount match yet the class column shows mismatch as seen below.
The logic seems fine because row 2 is correct, the value is present in app but not in bank therefore its missing in bank.
The image below shows a substraction of the amounts to identify if the values make 0.
Attached is the sql script.
So what i need is help in understanding why the class is incorrect.
with a as (
select
external_reference_id,
cast(debit_amount as int) as debit_amount ,
cast(credit_amount as int) as credit_amount ,
balance,
tbl_dt
from
s_finance.external_statement
),
b as(
select
externaltransactionid ,
cast(amount as int) as amount ,
tbl_dt
from
s_finance.end_of_day
where
transactiontype in ('DEPOSIT', 'WITHDRAWAL', 'TRANSFER_TO_ANY_BANK_ACCOUNT')
)
select
tbl_dt,
sum(debit_amount)/ 100 as debitamount,
sum(credit_amount)/ 100 as credit_amount ,
--issue could be here?
sum(appamount) as appamount,
class,
count(*) as recordcount
from
(
select
case
when a.tbl_dt is null then 'missing in Bank '
when b.tbl_dt is null then 'missing in eod'
when a.debit_amount <> b.amount then 'mismatch debit amount' --issue could be here?
when a.credit_amount <> b.amount then 'mismatch credit'
else 'match'
end as class,
coalesce(a.tbl_dt,b.tbl_dt)as tbl_dt,
coalesce (a.debit_amount,0) as debit_amount , --issue could be here?
coalesce (a.credit_amount,0) as credit_amount ,
coalesce(b.amount,0) as appamount
from
a
full join b on
cast(external_reference_id as varchar) = b.externaltransactionid
)
where
tbl_dt = 20240515
group by
tbl_dt,
classBlockquote