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 -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Jose Isaias Cabrera >Sent: Monday, 13 January, 2020 12:42 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Subject: [sqlite] Capturing the changes in columns in a table > > >Greetings! > >Please observe the following, > > >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'); > > >The SQL below provide info only for two dates (2019-02-11 and 2019-02- >12): > > >select new.a,old.b,new.b, 'column b changed on ' || new.idate as info >from t as new > > LEFT JOIN t as old ON > > new.idate = '2019-02-12' > >AND old.idate = '2019-02-11' > >AND new.a = old.a > >WHERE > > new.b != old.b > >UNION ALL > >select new.a,old.c,new.c, 'column c changed on ' || new.idate as info >from t as new > > LEFT JOIN t as old ON > > new.idate = '2019-02-12' > >AND old.idate = '2019-02-11' > >AND new.a = old.a > >WHERE > > new.c != old.c > >UNION ALL > >select new.a,old.d,new.d, 'column d changed on ' || new.idate from t as >new > > LEFT JOIN t as old ON > > new.idate = '2019-02-12' > >AND old.idate = '2019-02-11' > >AND new.a = old.a > >WHERE > > new.d != old.d > >UNION ALL > >select new.a,old.e,new.e, 'column e changed on ' || new.idate from t as >new > > LEFT JOIN t as old ON > > new.idate = '2019-02-12' > >AND old.idate = '2019-02-11' > >AND new.a = old.a > >WHERE > > new.e != old.e > >ORDER by new.a; > > >p001|1|10|column b changed on 2019-02-12 >p002|2|4|column c changed on 2019-02-12 >p003|n|y|column d changed on 2019-02-12 >sqlite> > >What I would like is to cover all of the dates in the same command, so >that the output is this, > >p001|1|10|column b changed on 2019-02-12 >p002|2|4|column c changed on 2019-02-12 >p003|n|y|column d changed on 2019-02-12 >p004|4|5|column b changed on 2019-02-13 >p004|2|3|column c changed on 2019-02-13 >p004|y|n|column d changed on 2019-02-13 >p005|2|3|column c changed on 2019-02-13 >p005|4|8|column e changed on 2019-02-13 > >Yes, I know I can get all the dates and build the SQL as I did above, but >is there a way to do this in one call? Thanks for the help. > >josé >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users