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
