On 2020/01/13 9:42 PM, Jose Isaias Cabrera wrote:
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'); ... 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.
Of course there is :) Many questions are not clear though.I assumed you are only interested in day-on-day changes, because in many days there can be many different values, if you measure them all against the last day, it will look like many changes between day X and the last day, when really you are probably interested only in the change from one day to the next day.
I also assumed that the days you will give as the input, let's call that min date and max date of the report, will refer to the days on which changed happened - which seems obvious, but the query must change if it is not the case.
Anyway, here is the SQL to achieve it. I made 2 queries, one in which it just gets the min and max from your data, and the other in which you can specify the min and max date. I guess the second one will be more what you want - the nice t hing is your calling program only needs to adjust those two values once, nowhere else:
(PS: I left the whole generated output in so you can see at the bottom, those errors are not errors, but just informing us that SQLite is creating indexes for those CTE tables, which is great and means this should be relatively fast on large datasets even.)
-- SQLite version 3.30.1 [ Release: 2019-10-10 ] on SQLitespeed version 2.1.3.11.
-- Script Items: 4 Parameter Count: 0-- 2020-01-14 00:08:17.875 | [Info] Script Initialized, Started executing... -- ================================================================================================
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')
,('p002', 2, 2, 'n', 4, '2019-02-11')
,('p003', 3, 2, 'n', 4, '2019-02-11')
,('p004', 4, 2, 'y', 4, '2019-02-11')
,('p005', 5, 2, 'y', 4, '2019-02-11')
,('p001',10, 2, 'n', 4, '2019-02-12')
,('p002', 2, 4, 'n', 4, '2019-02-12')
,('p003', 3, 2, 'y', 4, '2019-02-12')
,('p004', 4, 2, 'y', 4, '2019-02-12')
,('p005', 5, 2, 'y', 4, '2019-02-12')
,('p001',10, 2, 'n', 4, '2019-02-13')
,('p002', 2, 4, 'n', 4, '2019-02-13')
,('p003', 3, 2, 'y', 4, '2019-02-13')
,('p004', 5, 3, 'n', 4, '2019-02-13')
,('p005', 5, 3, 'y', 8, '2019-02-13')
;
-- This version guesses the min and max dates...
WITH DSpan(minDate, maxDate) AS (
SELECT MIN(idate), MAX(idate) FROM t GROUP BY idate
), DDays(dayOldDate, dayNewDate) AS (
SELECT minDate, date(minDate,'+1 day') FROM DSpan
UNION ALL
SELECT dayNewDate, date(dayNewDate,'+1 day') FROM DDays, DSpan WHERE
DDays.dayNewDate < DSpan.maxDate
), Chg(a, idate, col, oldVal, newVal) AS ( SELECT DISTINCT tNew.a, tNew.idate, 'b', tOld.b,tNew.b FROM DDays JOIN t AS tOld ON tOld.idate = DDays.dayoldDate JOIN t AS tNew ON tNew.idate = DDays.dayNewDate WHERE tNew.a = tOld.a AND tNew.b != tOld.b UNION ALL SELECT DISTINCT tNew.a, tNew.idate, 'c', tOld.c,tNew.c FROM DDays JOIN t AS tOld ON tOld.idate = DDays.dayoldDate JOIN t AS tNew ON tNew.idate = DDays.dayNewDate WHERE tNew.a = tOld.a AND tNew.c != tOld.c UNION ALL SELECT DISTINCT tNew.a, tNew.idate, 'd', tOld.d,tNew.d FROM DDays JOIN t AS tOld ON tOld.idate = DDays.dayoldDate JOIN t AS tNew ON tNew.idate = DDays.dayNewDate WHERE tNew.a = tOld.a AND tNew.d != tOld.d UNION ALL SELECT DISTINCT tNew.a, tNew.idate, 'e', tOld.e,tNew.e FROM DDays JOIN t AS tOld ON tOld.idate = DDays.dayoldDate JOIN t AS tNew ON tNew.idate = DDays.dayNewDate WHERE tNew.a = tOld.a AND tNew.e != tOld.e ) SELECT Chg.a, Chg.oldVal, Chg.newVal,('Column '||Chg.col||' changed on '||Chg.idate||' from '||Chg.oldVal||' to '||Chg.newVal||'.') AS Change
FROM Chg ORDER BY Chg.a, Chg.idate ; -- a | oldVal | newVal | Change -- ---- | ------ | ------ | -------------------------------------------- -- p001 | 1 | 10 | Column b changed on 2019-02-12 from 1 to 10. -- p002 | 2 | 4 | Column c changed on 2019-02-12 from 2 to 4. -- p003 | n | y | Column d changed on 2019-02-12 from n to y. -- p004 | 4 | 5 | Column b changed on 2019-02-13 from 4 to 5. -- p004 | 2 | 3 | Column c changed on 2019-02-13 from 2 to 3. -- p004 | y | n | Column d changed on 2019-02-13 from y to n. -- p005 | 2 | 3 | Column c changed on 2019-02-13 from 2 to 3. -- p005 | 4 | 8 | Column e changed on 2019-02-13 from 4 to 8. -- This version let's you set it, I chose the last two of the days here. WITH DSpan(minDate, maxDate) AS ( SELECT '2019-02-12', '2019-02-13' ), DDays(dayOldDate, dayNewDate) AS ( SELECT minDate, date(minDate,'+1 day') FROM DSpan UNION ALLSELECT dayNewDate, date(dayNewDate,'+1 day') FROM DDays, DSpan WHERE DDays.dayNewDate < DSpan.maxDate
), Chg(a, idate, col, oldVal, newVal) AS ( SELECT DISTINCT tNew.a, tNew.idate, 'b', tOld.b,tNew.b FROM DDays JOIN t AS tOld ON tOld.idate = DDays.dayoldDate JOIN t AS tNew ON tNew.idate = DDays.dayNewDate WHERE tNew.a = tOld.a AND tNew.b != tOld.b UNION ALL SELECT DISTINCT tNew.a, tNew.idate, 'c', tOld.c,tNew.c FROM DDays JOIN t AS tOld ON tOld.idate = DDays.dayoldDate JOIN t AS tNew ON tNew.idate = DDays.dayNewDate WHERE tNew.a = tOld.a AND tNew.c != tOld.c UNION ALL SELECT DISTINCT tNew.a, tNew.idate, 'd', tOld.d,tNew.d FROM DDays JOIN t AS tOld ON tOld.idate = DDays.dayoldDate JOIN t AS tNew ON tNew.idate = DDays.dayNewDate WHERE tNew.a = tOld.a AND tNew.d != tOld.d UNION ALL SELECT DISTINCT tNew.a, tNew.idate, 'e', tOld.e,tNew.e FROM DDays JOIN t AS tOld ON tOld.idate = DDays.dayoldDate JOIN t AS tNew ON tNew.idate = DDays.dayNewDate WHERE tNew.a = tOld.a AND tNew.e != tOld.e ) SELECT Chg.a, Chg.oldVal, Chg.newVal,('Column '||Chg.col||' changed on '||Chg.idate||' from '||Chg.oldVal||' to '||Chg.newVal||'.') AS Change
FROM Chg ORDER BY Chg.a, Chg.idate ; -- a | oldVal | newVal | Change -- ---- | ------ | ------ | ------------------------------------------- -- p004 | 4 | 5 | Column b changed on 2019-02-13 from 4 to 5. -- p004 | 2 | 3 | Column c changed on 2019-02-13 from 2 to 3. -- p004 | y | n | Column d changed on 2019-02-13 from y to n. -- p005 | 2 | 3 | Column c changed on 2019-02-13 from 2 to 3. -- p005 | 4 | 8 | Column e changed on 2019-02-13 from 4 to 8.-- Script Stats: Total Script Execution Time: 0d 00h 00m and 00.050s -- Total Script Query Time: 0d 00h 00m and 00.009s
-- Total Database Rows Changed: 15 -- Total Virtual-Machine Steps: 6990 -- Last executed Item Index: 4 -- Last Script Error:-- ------------------------------------------------------------------------------------------------
-- 2020-01-14 00:08:17.900 | [Success] Script Success. -- 2020-01-14 00:08:17.901 | [Success] Transaction Rolled back.-- ------- DB-Engine Logs (Contains logged information from all DB connections during run) ------ -- [2020-01-14 00:08:17.826] APPLICATION : Script E:\Documents\SQLiteAutoScript.sql started at 00:08:17.826 on 14 January. -- [2020-01-14 00:08:17.890] ERROR (284) : automatic index on DDays(dayOldDate)
-- [2020-01-14 00:08:17.890] ERROR (284) : automatic index on t(a)-- [2020-01-14 00:08:17.890] ERROR (284) : automatic index on DDays(dayOldDate)
-- [2020-01-14 00:08:17.890] ERROR (284) : automatic index on t(a)-- [2020-01-14 00:08:17.890] ERROR (284) : automatic index on DDays(dayOldDate)
-- [2020-01-14 00:08:17.891] ERROR (284) : automatic index on t(a)-- [2020-01-14 00:08:17.891] ERROR (284) : automatic index on DDays(dayOldDate)
-- [2020-01-14 00:08:17.891] ERROR (284) : automatic index on t(a)-- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on DDays(dayOldDate)
-- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on t(a)-- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on DDays(dayOldDate)
-- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on t(a)-- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on DDays(dayOldDate)
-- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on t(a)-- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on DDays(dayOldDate)
-- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on t(a)-- ================================================================================================
_______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
