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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
