Re: Capturing the changes in columns in a table

Jean-Luc Hainaut, on Tuesday, January 14, 2020 07:25 AM, wrote...
>
>
> Another version that doesn't use CTE nor window functions:
>
> select t1.a as Proj, t2.idate as "On", 'b' as Var, t1.b as oldVal, t2.b
> as newVal
> from   t t1,t t2
> where  t1.a = t2.a
> and    t2.idate = date(t1.idate,'+1 day')
> and    t1.b <> t2.b
>     union all
> select t1.a as Proj, t2.idate as "On", 'c' as Var, t1.c as oldVal, t2.c
> as newVal
> from   t t1,t t2
> where  t1.a = t2.a
> and    t2.idate = date(t1.idate,'+1 day')
> and    t1.c <> t2.c
>     union all
> select t1.a as Proj, t2.idate as "On", 'd' as Var, t1.d as oldVal, t2.d
> as newVal
> from   t t1,t t2
> where  t1.a = t2.a
> and    t2.idate = date(t1.idate,'+1 day')
> and    t1.d <> t2.d
>     union all
> select t1.a as Proj, t2.idate as "On", 'e' as Var, t1.e as oldVal, t2.e
> as newVal
> from   t t1,t t2
> where  t1.a = t2.a
> and    t2.idate = date(t1.idate,'+1 day')
> and    t1.e <> t2.e
> order by Proj,"On";
>
> Valid if there is one state for each project on each date.

Thanks, Jean-Luc.  This one is also pretty fast. Interesting.

josé
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users