Compare 2 sub queries and return value if there has been a change

1
Summary
Summary

Hi all,

I need SQL to compare the below 2 sub queries and then only show values that are different and or new in the 'latest' (subquery2) subquery. Both subqueries use the same table to SELECT FROM:

Subquery 1:

select tra_rac_accno, tra_cr
from transactions
join admin_periods on trunc(sysdate-7) between ape_start_date and ape_end_date
and ape_admin_period_type = 'BAL'
and ape_aye_aun_code = 'CANTERBURYW'
and tra_date between ape_start_date and ape_end_Date
where tra_trt_code = 'HBS'
and tra_rac_accno not in ('2')
order by tra_rac_accno ASC

Subquery 2:

select tra_rac_accno, tra_cr
from transactions
join admin_periods on trunc(sysdate) between ape_start_date and ape_end_date
and ape_admin_period_type = 'BAL'
and ape_aye_aun_code = 'CANTERBURYW'
and tra_date between ape_start_date and ape_end_Date
where tra_trt_code = 'HBS'
and tra_rac_accno not in ('2')
order by tra_rac_accno ASC

Subquery 1 Output:

tra_rac_accno tra_cr
318 66.7
323 97.67
328 107.26
336 96.95
340 80.14

Subquery 2 Output:

tra_rac_accno tra_cr
318 69.5
323 97.67
328 107.26
336 96.95
340 50.20

I want to see the following output:

tra_rac_accno tra_cr
318 69.5
340 50.20

I appreciate all who take time to read this post and also respond with assistance. THANK YOU!

Subquery

2

If you are using SQL Server, you can use the EXCEPT clause.

<Your second query here>
EXCEPT
<Your first query here>

There may be simpler ways to write the query, but it looks like you are using Oracle, and I am a novice with Oracle, so I won't even try.

1 Like
3

Thanks for your input JamesK - I googled the Oracle equivalent to EXCEPT and will give MINUS a shot and see how it goes.