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

Reply via email to