R Smith, on Monday, January 13, 2020 05:25 PM, wrote...
> 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, on
> ), 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 ALL
> SELECT dayNewDate, date(dayNewDate,'+1 day') FROM DDays, DSpan WHERE
> DDays.dayNewDate, on
> ), 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)
> --
Wow! Thanks for this. I had not thought about your questions. My boss said,
I need to know all the changes per project whenever it happened. So,... I will
have to revise my thinking, but I have enough with your help to continue. I am
going to have to reload SQLitespeed, and try it again. :-) Thanks.
josé
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
