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 <[email protected]> On
>Behalf Of Jose Isaias Cabrera
>Sent: Monday, 13 January, 2020 12:42
>To: SQLite mailing list <[email protected]>
>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
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
