Note this only requires that "idate" be a unique orderable sequence within "a" in order to work. It does not have to be particular (such as a date/datetime). It can be a date, a datetime, an integer (as in unixtime), a real (as in julianday number), or any old sequence number and it will still work exactly the same.
Replace UNION with UNION ALL and add an ORDER BY clause at the end which may help performance depending on the amount of data. create unique index i on t (a, idate) to optimize retrieval. -- 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 Keith Medcalf >Sent: Monday, 13 January, 2020 16:51 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Subject: Re: [sqlite] Capturing the changes in columns in a table > > >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users