Re: [sqlite] Capturing the changes in columns in a table

[R Smith]

Jose, I like Keith's version better using the Windowing functions 
assuming your version of SQLite is newer than 3.27 (or whenever Window 
functions were introduced, again my memory fails...)

Most importantly, the CTE query /requires/ changes be day-on-day to be 
seen, which is the case in your example, but might not be the real life 
case, whereas this window-function query of Keith will spot changes even 
if updates are logged days apart, or indeed happened on the same day.

You still need to make sure of a few things, but I would definitely use 
this query.


On 2020/01/14 1:51 AM, Keith Medcalf wrote:
create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p001', 10, 2, 'n', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 4, 'n', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'y', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, 
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p001', 10, 2, 'n', 4, 
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 4, 'n', 4, 
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'y', 4, 
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p004', 5, 3, 'n', 4, 
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 3, 'y', 8, 
'2019-02-13');

   select *
     from (
           select a,
                  idate,
                  'b' as variable,
                  lag(b, 1) over (partition by a order by idate) as oldv,
                  b as newv
             from t
        union
           select a,
                  idate,
                  'c' as variable,
                  lag(c, 1) over (partition by a order by idate) as oldv,
                  c as newv
             from t
        union
           select a,
                  idate,
                  'd' as variable,
                  lag(d, 1) over (partition by a order by idate) as oldv,
                  d as newv
             from t
        union
           select a,
                  idate,
                  'e' as variable,
                  lag(e, 1) over (partition by a order by idate) as oldv,
                  e as newv
             from t
          )
    where oldv <> newv;

a           idate       variable    oldv        newv
----------  ----------  ----------  ----------  ----------
p001        2019-02-12  b           1           10
p002        2019-02-12  c           2           4
p003        2019-02-12  d           n           y
p004        2019-02-13  b           4           5
p004        2019-02-13  c           2           3
p004        2019-02-13  d           y           n
p005        2019-02-13  c           2           3
p005        2019-02-13  e           4           8

Change "<>" to "IS NOT" to find out when their was no previous value

_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users