SQL Server Retrieving Where a Data Object Was Based Over a Period of Time Based on Track Information

Question

I have doubts about how to do this in SQL Server 2008 R2.

I have a table with multiple inputs, and these inputs have a Parent tag and a timestamp.

Sometimes these objects have their own parent tag, changed in the timestamp. This parent tag may change from time to time. Suppose I have a table below. My current table contains millions of data with different objects. When you see the table, it is easy to see that the ParentID has been changed in timestamps from 3 to 4, from 6 to 7, and from 8 to 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 I want is the one to which the ObjectID belongs, as well as the start and end timestamp and the 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 I have still happens when a change occurs, but it only gives me the changes, but not the table above.

 ObjectID ParentID_Old ParentID_New DateID_Changed -------- ------------ ------------ ------------ 100 1 2 3 to 4 100 2 3 6 to 7 100 3 1 8 to 9 

Here is the code for creating a table and test inserts. Below also select "Select" to receive changes.

  --Initial Insert Code IF OBJECT_ID('tempdb..#Trackings') Is Not Null Drop table #Trackings Create Table #Trackings ( ProductID bigint , value float , StoreID int , DateID int , Aux_Row_Number int ) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,1,1,325.2,1) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,1,2,326.2,2) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,1,3,329.6,3) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,4,335.2,4) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,5,336.5,5) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,6,338.3,6) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,3,7,339.2,7) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,3,8,342.1,8) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,1,9,343.7,9) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,1,10,355.0,10) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,1,12,385.0,12) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,3,13,485.0,13) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,3,14,985.0,14) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,3,15,1585.0,15) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,3,16,3585.0,16) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,17,5585.0,17) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,18,6585.0,18) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,19,8585.0,19) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,20,9585.0,20) 

And SQL to get the changes I use:

 Select ISNULL(A.StoreID,-1) , ISNULL(B.StoreID,-1) , A.ProductID , A.value , B.value , A.DateID , B.DateID From #Trackings A Join #Trackings B On A.ProductID = B.ProductID And A.Aux_Row_Number + 1 = B.Aux_Row_Number And ISNULL(A.StoreID,-1) <> ISNULL(B.StoreID,-1) 

Any ideas for light guys?

Thanks in advance!

EDITED: A little more information about the “business”: ParentID will look like the store where the product is located, and DateID is the time it arrived there. Suppose productID 100 is in ParentID 1, which means that in DateID 1, the product ID 100 is entered in Store 1. Therefore, for some reason, it moved to Store 2 in DatedID 4. So my first row in the answer table means that ProductID 100 was in StoreID 1 from DateID 1 to DateID 4. Then, Product ID 100 remained in StoredID 2 from DateID 4 to 7, and then changed to StoredID 3 and finally it returned to StoreID 1 from DateID 9 to ours the last DateID in the "Date Sensor" field is "selected." Therefore, so that the response table has 2 rows with ParentID 1.

Answer 1

I finally found a solution based on my initial table, better performance than using CTE, and as said, https://stackoverflow.com/users/2522030/mike-abramczyk (a real table has 5k entries and using it offers lasted a long time).

After querying the lookup table, I added two rows to the table for each ProductID. These strings will receive a dummy StoreID (i.e. -9999): one with Min (DateID) - 1 and another with Max (DateID) + 1.

 Insert into #Trackings (Aux_Row_Number,StoreID,DateID,ProductID,value) Select Min(Aux_Row_Number)-1 Aux_Row_Number,-9999 as StoreID, min(DateID)-1 as DateID,ProductID,Min(value) From #Trackings group by ProductID Order by ProductID Insert into #Trackings (Aux_Row_Number,StoreID,DateID,ProductID,value) Select Max(Aux_Row_Number)+1 Aux_Row_Number,-9999 as StoreID, max(DateID)+1 as DateID,ProductID,Max(value) From #Trackings group by ProductID Order by ProductID 

Then I used the request you submitted to get the changes. Thus, I could get Change from Dummy (-9999) in real StoreID (1) and the last change from real StoreID (3) to Dummy (-9999).

 select ISNULL(A.StoreID,-1) , ISNULL(B.StoreID,-1) , A.ProductID , A.value , B.value , A.DateID , B.DateID , ROW_NUMBER() OVER (Partition by B.ProductID Order by A.DateID) from #Trackings A Join #Trackings B On A.ProductID = B.ProductID And A.Aux_Row_Number + 1 = B.Aux_Row_Number AND ISNULL(A.StoreID,0) <> ISNULL(B.StoreID ,0) 

It was a decisive step! Now I can create a results table with the DateIDs of the changes I was looking for. The Aux_Row_Number column helped to get the sequence of changes for each product using (I think the last query created the #ProductStoreChanges table - I will post all Soltion below):

 select A.ID_FinalStore,A.DateID_Final,B.DateID_Final,A.ProductID,B.value_2,A.value_2,B.value_2 - A.value_2 DeltaValue from #ProductStoreChanges A Join #ProductStoreChanges B On (A.Aux_Row_Number + 1 = B.Aux_Row_Number) And A.ProductID = B.ProductID Order by A.DateID_Final 

Here is the final solution:

 IF OBJECT_ID('tempdb..#Trackings') Is Not Null Drop table #Trackings IF OBJECT_ID('tempdb..#ProductStoreChanges') Is Not Null Drop table #ProductStoreChanges Create Table #Trackings ( ProductID bigint , value float , StoreID int , DateID int , Aux_Row_Number int , flg_changed bit Default(0) ) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,1,1,325.2,1) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,1,2,326.2,2) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,1,3,329.6,3) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,4,335.2,4) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,5,336.5,5) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,6,338.3,6) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,3,7,339.2,7) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,3,8,342.1,8) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,1,9,343.7,9) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,1,10,355.0,10) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,1,12,385.0,12) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,3,13,485.0,13) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,3,14,985.0,14) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,3,15,1585.0,15) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,3,16,3585.0,16) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,17,5585.0,17) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,18,6585.0,18) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,19,8585.0,19) Insert into #Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,20,9585.0,20) Insert into #Trackings (Aux_Row_Number,StoreID,DateID,ProductID,value) Select Min(Aux_Row_Number)-1 Aux_Row_Number,-9999 as StoreID, min(DateID)-1 as DateID,ProductID,Min(value) From #Trackings group by ProductID Order by ProductID Insert into #Trackings (Aux_Row_Number,StoreID,DateID,ProductID,value) Select Max(Aux_Row_Number)+1 Aux_Row_Number,-9999 as StoreID, max(DateID)+1 as DateID,ProductID,Max(value) From #Trackings group by ProductID Order by ProductID CREATE TABLE #ProductStoreChanges ( ID_InitialStore INT , ID_FinalStore INT , ProductID INT , value_1 BIGINT , value_2 BIGINT , DateID_Initial BIGINT , DateID_Final BIGINT , Aux_Row_Number INT ) INSERT INTO #ProductStoreChanges ( ID_InitialStore , ID_FinalStore , ProductID , value_1 , value_2 , DateID_Initial , DateID_Final , Aux_Row_Number ) select ISNULL(A.StoreID,-1) , ISNULL(B.StoreID,-1) , A.ProductID , A.value , B.value , A.DateID , B.DateID , ROW_NUMBER() OVER (Partition by B.ProductID Order by A.DateID) from #Trackings A Join #Trackings B On A.ProductID = B.ProductID And A.Aux_Row_Number + 1 = B.Aux_Row_Number AND ISNULL(A.StoreID,0) <> ISNULL(B.StoreID ,0) select A.ID_FinalStore,A.DateID_Final,B.DateID_Final,A.ProductID,B.value_2,A.value_2,B.value_2 - A.value_2 DeltaValue from #ProductStoreChanges A Join #ProductStoreChanges B On (A.Aux_Row_Number + 1 = B.Aux_Row_Number) And A.ProductID = B.ProductID Order by A.DateID_Final 

Answer 2

OK, try this using the updated sample data:

 Declare @Trackings table ( ProductID bigint , value float , StoreID int , DateID int , Aux_Row_Number int ) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,1,1,325.2,1) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,1,2,326.2,2) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,1,3,329.6,3) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,4,335.2,4) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,5,336.5,5) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,6,338.3,6) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,3,7,339.2,7) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,3,8,342.1,8) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,1,9,343.7,9) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,1,10,355.0,10) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,1,12,385.0,12) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,3,13,485.0,13) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,3,14,985.0,14) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,3,15,1585.0,15) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,3,16,3585.0,16) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,17,5585.0,17) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,18,6585.0,18) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,19,8585.0,19) Insert into @Trackings(ProductID,StoreID,DateID,value,Aux_Row_Number) Values (100,2,20,9585.0,20) ; with t as (select *, ROW_NUMBER() over (order by dateid) as rn from @Trackings ), cte1 (Productid, Storeid, DateID, value, rn, set1) as (select ProductID, StoreID, DateID, value, rn , 1 from t where rn = 1 union all select t.productID, t.storeID, t.dateID, t.value, t.rn, case when cte1.Storeid = t.storeID then cte1.set1 else cte1.set1+1 end from t join cte1 on t.rn = cte1.rn+1) , t2 as (select Productid, Storeid, set1, MIN(CAST(DateID as int)) as tmi, max(dateid) as tma from cte1 group by Productid, Storeid, set1) select t3.Productid, t3.Storeid, t3.set1, t3.date_min, t3.date_max, u.value - t.value from (select a.Productid, a.Storeid, a.set1, a.tmi as date_min, coalesce(b.tmi, a.tma) as date_max from t2 a 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 by set1 

The column "Value" confused me, because you use commas (,) instead of periods (.) To separate the integer from the decimal part of your float.