So I am validating count between source and target tables, if count is not the same it can go in positive or negative number, however when is negative it would show NULL. I used to know this but seems to me I have short memory. My code is below.
with cte as (
select 'source' [object],count(contract_id) as total_count
FROM [account].[dbo].[account] act
left join [account].[dbo].[contract] cont
on act.account_id = cont.account_id)
,cte1 as(
select 'target' [object], count(contract_id) as total_count
from [account].[dbo].[action]
union
select * from cte)
select * from cte1
union
select 'diff' [object],
(select total_count from cte where [object] = 'source')
-
(select total_count from cte where [object] = 'target')
Results is as follow object total_count target 28402 source 28401 diff NULL -- here it should be negative 1 (-1)