Compare two SQL tables and return count of rows with changes

I have two partitions from an SQL table containing num_key records. I need to compare and count changes in the February records versus the January records.

SAMPLE DATA AND DESIRED RESULTS:

ptn_dt = ‘2019-01-31’ (January)

num_key	active_indicator
111	true
112	false
113	false
114	false
115	true
116	true

ptn_dt = ‘2019-02-28’ (February)

num_key	active_indicator
111	true
112	false
113	true
114	true
115	true
116	true
117	true
118	false
119	true

EXPECTED OUTPUT:

Count of NEW entries (num_key in Feb but not in Jan) with active_indicator = ‘true’ —> 2 (pertains to num_key 117 and 119)

Count of entries WITH CHANGE in active_indicator (false to true) between Jan and Feb —> 2 (pertains to num_key 113 and 114

Count of entries WITH NO CHANGE in active_indicator (true to true) between Jan and Feb —> 3 (pertains to num_key 111, 115 and 116

What SQL query could I use? I need to get the count of all active_indicator=true in the February partition but divided into 3 outputs (new entries, false to true from Jan to Feb, and true to true from Jan to Feb).

Answer

Use full join (Full join returns joined records, plus not joined from left table, plus not joined from right table). Use case expressions with count():

select
       count(case when t1.num_key is null then 1 else null end) as cnt_new,
       count(case when t1.active_indicator = false and t2.active_indicator = true then 1 else null end) as cnt_false_to_true,
       count(case when t1.active_indicator = true and  t2.active_indicator = true then 1 else null end) as cnt_true_not_changed
 from (select * from table t1 where t1.ptn_dt = '2019-01-31') t1
      full join (select * from table t2 where ptn_dt = '2019-02-28' ) t2
           on t1.num_key = t2.num_key