SQL Server Getting where an data object was based in a Period of Time given Track Info -
i have doubt on how conclude in sql server 2008 r2.
i have table has inputs , inputs have parent tag , timestamp.
sometimes these objects have parent tag changed in timestamp. parent tag can change time time. let´s suppose have table below. current table has millions of data different objectids. seeing table, easy see parentid changed in timestamps 3 4, 6 7 , 8 9.
productid parentid dateid value -------- --------- ------- ------------- 100 1 1 325,2 100 1 2 326,2 100 1 3 329,6 100 2 4 335,2 100 2 5 336,5 100 2 6 338,3 100 3 7 339,2 100 3 8 342,1 100 1 9 343,7 100 1 10 355,6 100 1 11 385,8 the answer want parentid objectid belonged , start , end timestamp , delta value between timestamps (timestamp = ts)
productid parentid dateid_start dateid_end deltavalue -------- --------- ---------- -------- ---------- 100 1 1 4 10,0 100 2 4 7 4,0 100 3 7 9 4,5 100 1 9 11 42,1 what have accomplish far getting when there change, gives me changes, not table above.
objectid parentid_old parentid_new dateid_changed -------- ------------ ------------ ------------ 100 1 2 3 4 100 2 3 6 7 100 3 1 8 9 here code generate table , test inserts. below select changes.
--initial insert code if object_id('tempdb..#trackings') not null drop table #trackings create table #trackings ( productid bigint , value float , storeid int , dateid int , aux_row_number int ) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,1,1,325.2,1) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,1,2,326.2,2) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,1,3,329.6,3) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,2,4,335.2,4) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,2,5,336.5,5) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,2,6,338.3,6) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,3,7,339.2,7) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,3,8,342.1,8) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,1,9,343.7,9) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,1,10,355.0,10) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,1,12,385.0,12) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,3,13,485.0,13) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,3,14,985.0,14) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,3,15,1585.0,15) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,3,16,3585.0,16) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,2,17,5585.0,17) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,2,18,6585.0,18) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,2,19,8585.0,19) insert #trackings(productid,storeid,dateid,value,aux_row_number) values (100,2,20,9585.0,20) and sql changes using:
select isnull(a.storeid,-1) , isnull(b.storeid,-1) , a.productid , a.value , b.value , a.dateid , b.dateid #trackings join #trackings b on a.productid = b.productid , a.aux_row_number + 1 = b.aux_row_number , isnull(a.storeid,-1) <> isnull(b.storeid,-1) any lights ideas guys?
thanks in advance!
edited: little bit more "business" info: parentid store product , dateid time arrived there. let's suppose productid 100 in parentid 1, means in dateid 1 productid 100 entered in store 1. reason moved store 2 in datedid 4. first row in answer table means productid 100 in storeid 1 dateid 1 dateid 4. productid 100 stayed in storedid 2 dateid 4 7, changed storedid 3 , came storeid 1 dateid 9 our last dateid in dateid range "selected". that's why answer table has 2 lines parentid 1.
ok, try this, using updated sample data:
declare @trackings table ( productid bigint , value float , storeid int , dateid int , aux_row_number int ) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,1,1,325.2,1) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,1,2,326.2,2) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,1,3,329.6,3) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,2,4,335.2,4) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,2,5,336.5,5) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,2,6,338.3,6) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,3,7,339.2,7) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,3,8,342.1,8) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,1,9,343.7,9) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,1,10,355.0,10) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,1,12,385.0,12) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,3,13,485.0,13) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,3,14,985.0,14) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,3,15,1585.0,15) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,3,16,3585.0,16) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,2,17,5585.0,17) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,2,18,6585.0,18) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,2,19,8585.0,19) insert @trackings(productid,storeid,dateid,value,aux_row_number) values (100,2,20,9585.0,20) ; t (select *, row_number() on (order dateid) rn @trackings ), cte1 (productid, storeid, dateid, value, rn, set1) (select productid, storeid, dateid, value, rn , 1 t rn = 1 union select t.productid, t.storeid, t.dateid, t.value, t.rn, case when cte1.storeid = t.storeid cte1.set1 else cte1.set1+1 end t join cte1 on t.rn = cte1.rn+1) , t2 (select productid, storeid, set1, min(cast(dateid int)) tmi, max(dateid) tma cte1 group productid, storeid, set1) select t3.productid, t3.storeid, t3.set1, t3.date_min, t3.date_max, u.value - t.value (select a.productid, a.storeid, a.set1, a.tmi date_min, coalesce(b.tmi, a.tma) date_max t2 left join t2 b on a.set1 + 1 = b.set1) t3 join @trackings t on t3.date_min = t.dateid join @trackings u on t3.date_max = u.dateid order set1 the "value" column confused me, using commas (,) instead of periods (.) separate integer decimal part of float.
Comments
Post a Comment