1

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).

    leftjoin
    • 36,950
    • 8
    • 57
    • 116
    JM DR
    • 43
    • 6
    • maybe first you should `JOIN` them by `num_key` (but I don't remeber if it sould be `INNER JOIN`, `OUTER JOIN`, or other) and for every `num_key` you will have both values (FEB, JAN) in one row. So you can compare them to get with changes, and without changes. If you get `None` in `JAN` then you will know it is new value in `FEB`. – furas May 04 '21 at 10:09
    • @JMDR . . . I answered this question already. – Gordon Linoff May 04 '21 at 11:29

    1 Answers1

    1

    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
    leftjoin
    • 36,950
    • 8
    • 57
    • 116