If calculation is negative it shows NULL, how to fix this? 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)
The problem is here:

(select total_count from cte where [object] = 'target')

There is not object = 'target' in the &quot;cte&quot; cte.
As the select returned null, the calculation also have to return null.
Try this: (changing cte for cte1)
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 cte1 where [object] = 'target')