James K. Lowden, on Tuesday, February 12, 2019 11:39 AM, wrote...
>On Tue, 12 Feb 2019 15:05:29 +0000
>Jose Isaias Cabrera <jic...@outlook.com> wrote:
>
>> >SELECT * From t WHERE datestamp = "20190208"
>>
>> Ok, Simon, I'll bite; :-) Imagine this table:
>>
>> t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate)
>
>That's better.
>
>> how would I find the differences in the fields based on the different
>> idate?
>
>select ...
>from t as now join t as then
>on now.idate = '20190208'
>and then.idate = '20190207' -- note single quotes
>and ... -- other things that match
>where ... --- things that don't match
>
>Can't be more specific than that when the question is "find the
>differences".
This is the last fix on this,
select new.a,old.b,new.b, 'difference in column b' 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, 'difference in column c' 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.d,old.d,new.d, 'difference in column d' 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.d != old.d
UNION ALL
select new.a,old.e,new.e, 'difference in column e' 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.e != old.e
ORDER by new.a;
Result,
p001|2|4|difference in column c
p001|4|3|difference in column e
p002|2|4|difference in column c
p003|2|4|difference in column c
p004|2|4|difference in column c
p005|5|3|difference in column b
p005|2|3|difference in column c
This is the best way of doing this, correct? Thanks.
josé
PS: I've learn a few things in this last 7 days or so. Thanks so much you
Gurus.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
