How do I update a second table from a cte so that it works reliably









up vote
0
down vote

favorite












It would be great to get some advice on the following Robot performance data problem that is driving me crazy! We have 23 Robots in 5 Facilities and Spot data (placement and pickup locations) from all of them is collected into one big table. I have two tables #Temp2 and #Temp2d as follows:



#Temp2
 ID Spot LastDiff
76544 21 23
76545 21 0
76546 23 21
76547 23 0
76548 21 35
76549 23 21
76550 21 23
76551 23 21

#Temp2d
IDmin Spot LastDiff
76544 21 23
76546 23 21
76548 21 23
76549 23 21
76550 21 23
76551 23 21


The table #Temp2d is derived from #Temp2 and records the individual changes in Spot number (repeat Spot numbers have been removed). (The 90,000 record Spot number data is partitioned by two other columns not shown, FacilityName and Robot).
I'm then adding a new column "LastDiff" to #Temp2 to hold the last different value of Spot before it changed. To update the LastDiff column of #Temp2 the following update query is used:



;with cte_1 as
 (
 select t1.IDmin, t1.spot, t1.lastDiff
 from
 (
 select 
 IDmin
 , spot
 , LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot) AS lastDiff

 from #Temp2d
 ) t1
 ) 
 update #Temp2 set #Temp2.lastDiff = cte_1.lastDiff
 from cte_1
 where #Temp2.ID = cte_1.IDmin


The problem is that in the 90,000 row dataset around 30 values of LastDiff in #Temp2 are wrong, as shown in row 76,548 in the above #Temp2 table. There does not seem to be any pattern to the errors, they occur randomly throughout the dataset. Any assistance is resolving this problem would be hugely appreciated.






tsql sql-update common-table-expression partitioning







share|improve this question



















  • 2




    Might be, that the ORDER BY in your LAG()'s OVER-clause is not correct. You must use a column to order which ensures the sorting you need...
    – Shnugo
    yesterday










  • ha ha thanks so much - talk about overlooking the obvious! adding IDmin to the partition order-by did the trick: LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot, IDmin) AS lastDiff
    – Fyll See
    23 hours ago














up vote
0
down vote

favorite












It would be great to get some advice on the following Robot performance data problem that is driving me crazy! We have 23 Robots in 5 Facilities and Spot data (placement and pickup locations) from all of them is collected into one big table. I have two tables #Temp2 and #Temp2d as follows:



#Temp2
 ID Spot LastDiff
76544 21 23
76545 21 0
76546 23 21
76547 23 0
76548 21 35
76549 23 21
76550 21 23
76551 23 21

#Temp2d
IDmin Spot LastDiff
76544 21 23
76546 23 21
76548 21 23
76549 23 21
76550 21 23
76551 23 21


The table #Temp2d is derived from #Temp2 and records the individual changes in Spot number (repeat Spot numbers have been removed). (The 90,000 record Spot number data is partitioned by two other columns not shown, FacilityName and Robot).
I'm then adding a new column "LastDiff" to #Temp2 to hold the last different value of Spot before it changed. To update the LastDiff column of #Temp2 the following update query is used:



;with cte_1 as
 (
 select t1.IDmin, t1.spot, t1.lastDiff
 from
 (
 select 
 IDmin
 , spot
 , LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot) AS lastDiff

 from #Temp2d
 ) t1
 ) 
 update #Temp2 set #Temp2.lastDiff = cte_1.lastDiff
 from cte_1
 where #Temp2.ID = cte_1.IDmin


The problem is that in the 90,000 row dataset around 30 values of LastDiff in #Temp2 are wrong, as shown in row 76,548 in the above #Temp2 table. There does not seem to be any pattern to the errors, they occur randomly throughout the dataset. Any assistance is resolving this problem would be hugely appreciated.






tsql sql-update common-table-expression partitioning







share|improve this question



















  • 2




    Might be, that the ORDER BY in your LAG()'s OVER-clause is not correct. You must use a column to order which ensures the sorting you need...
    – Shnugo
    yesterday










  • ha ha thanks so much - talk about overlooking the obvious! adding IDmin to the partition order-by did the trick: LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot, IDmin) AS lastDiff
    – Fyll See
    23 hours ago












up vote
0
down vote

favorite









up vote
0
down vote

favorite











It would be great to get some advice on the following Robot performance data problem that is driving me crazy! We have 23 Robots in 5 Facilities and Spot data (placement and pickup locations) from all of them is collected into one big table. I have two tables #Temp2 and #Temp2d as follows:



#Temp2
 ID Spot LastDiff
76544 21 23
76545 21 0
76546 23 21
76547 23 0
76548 21 35
76549 23 21
76550 21 23
76551 23 21

#Temp2d
IDmin Spot LastDiff
76544 21 23
76546 23 21
76548 21 23
76549 23 21
76550 21 23
76551 23 21


The table #Temp2d is derived from #Temp2 and records the individual changes in Spot number (repeat Spot numbers have been removed). (The 90,000 record Spot number data is partitioned by two other columns not shown, FacilityName and Robot).
I'm then adding a new column "LastDiff" to #Temp2 to hold the last different value of Spot before it changed. To update the LastDiff column of #Temp2 the following update query is used:



;with cte_1 as
 (
 select t1.IDmin, t1.spot, t1.lastDiff
 from
 (
 select 
 IDmin
 , spot
 , LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot) AS lastDiff

 from #Temp2d
 ) t1
 ) 
 update #Temp2 set #Temp2.lastDiff = cte_1.lastDiff
 from cte_1
 where #Temp2.ID = cte_1.IDmin


The problem is that in the 90,000 row dataset around 30 values of LastDiff in #Temp2 are wrong, as shown in row 76,548 in the above #Temp2 table. There does not seem to be any pattern to the errors, they occur randomly throughout the dataset. Any assistance is resolving this problem would be hugely appreciated.






tsql sql-update common-table-expression partitioning







share|improve this question















It would be great to get some advice on the following Robot performance data problem that is driving me crazy! We have 23 Robots in 5 Facilities and Spot data (placement and pickup locations) from all of them is collected into one big table. I have two tables #Temp2 and #Temp2d as follows:



#Temp2
 ID Spot LastDiff
76544 21 23
76545 21 0
76546 23 21
76547 23 0
76548 21 35
76549 23 21
76550 21 23
76551 23 21

#Temp2d
IDmin Spot LastDiff
76544 21 23
76546 23 21
76548 21 23
76549 23 21
76550 21 23
76551 23 21


The table #Temp2d is derived from #Temp2 and records the individual changes in Spot number (repeat Spot numbers have been removed). (The 90,000 record Spot number data is partitioned by two other columns not shown, FacilityName and Robot).
I'm then adding a new column "LastDiff" to #Temp2 to hold the last different value of Spot before it changed. To update the LastDiff column of #Temp2 the following update query is used:



;with cte_1 as
 (
 select t1.IDmin, t1.spot, t1.lastDiff
 from
 (
 select 
 IDmin
 , spot
 , LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot) AS lastDiff

 from #Temp2d
 ) t1
 ) 
 update #Temp2 set #Temp2.lastDiff = cte_1.lastDiff
 from cte_1
 where #Temp2.ID = cte_1.IDmin


The problem is that in the 90,000 row dataset around 30 values of LastDiff in #Temp2 are wrong, as shown in row 76,548 in the above #Temp2 table. There does not seem to be any pattern to the errors, they occur randomly throughout the dataset. Any assistance is resolving this problem would be hugely appreciated.






tsql sql-update common-table-expression partitioning




tsql sql-update common-table-expression partitioning






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited 23 hours ago

























asked yesterday









Fyll See

297




297







  • 2




    Might be, that the ORDER BY in your LAG()'s OVER-clause is not correct. You must use a column to order which ensures the sorting you need...
    – Shnugo
    yesterday










  • ha ha thanks so much - talk about overlooking the obvious! adding IDmin to the partition order-by did the trick: LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot, IDmin) AS lastDiff
    – Fyll See
    23 hours ago












  • 2




    Might be, that the ORDER BY in your LAG()'s OVER-clause is not correct. You must use a column to order which ensures the sorting you need...
    – Shnugo
    yesterday










  • ha ha thanks so much - talk about overlooking the obvious! adding IDmin to the partition order-by did the trick: LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot, IDmin) AS lastDiff
    – Fyll See
    23 hours ago







2




2




Might be, that the ORDER BY in your LAG()'s OVER-clause is not correct. You must use a column to order which ensures the sorting you need...
– Shnugo
yesterday




Might be, that the ORDER BY in your LAG()'s OVER-clause is not correct. You must use a column to order which ensures the sorting you need...
– Shnugo
yesterday












ha ha thanks so much - talk about overlooking the obvious! adding IDmin to the partition order-by did the trick: LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot, IDmin) AS lastDiff
– Fyll See
23 hours ago




ha ha thanks so much - talk about overlooking the obvious! adding IDmin to the partition order-by did the trick: LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot, IDmin) AS lastDiff
– Fyll See
23 hours ago

















active

oldest

votes











Your Answer






StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);













 

draft saved


draft discarded


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53222104%2fhow-do-i-update-a-second-table-from-a-cte-so-that-it-works-reliably%23new-answer', 'question_page');

);

Post as a guest






















active

oldest

votes













active

oldest

votes









active

oldest

votes






active

oldest

votes















 

draft saved


draft discarded















































 


draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53222104%2fhow-do-i-update-a-second-table-from-a-cte-so-that-it-works-reliably%23new-answer', 'question_page');

);

Post as a guest

































































-

這個網誌中的熱門文章

How to combine Bézier curves to a surface?

圖片
Clash Royale CLAN TAG #URR8PPP up vote 2 down vote favorite My aim is to smooth the terrain in a video game. Therefore I contrived an algorithm that makes use of Bézier curves of different orders. But this algorithm is defined in a two dimensional space for now. To shift it into the third dimension I need to somehow combine the Bézier curves. Given two curves in each two dimensions, how can I combine them to build a surface? I thought about something like a curve of a curve. Or maybe I could multiply them somehow. How can I combine them to cause the wanted behavior? Is there a common approach? In the image you can see the input, on the left, and the output, on the right, of the algorithm that works in a two dimensional space. For the real application there is a three dimensional input. The algorithm relays only on the adjacent tiles. Given these it will define the control points of the mentioned Bézier cur...
閱讀完整內容

Mutual Information Always Non-negative

圖片
Clash Royale CLAN TAG #URR8PPP up vote 10 down vote favorite 6 What is the simplest proof that mutual information is always non-negative? i.e., $I(X;Y)ge0$ probability-theory share | cite | improve this question edited Jun 17 '12 at 15:38 Cameron Buie 83.6k 7 71 154 asked Jun 17 '12 at 15:30 Omri 359 3 13 3 Convexity of the function $tmapsto tlog t$. – Did Jun 17 '12 at 15:33 In addition, the convexity properties require the coefficients in the linear combination sum 1. Then, as p(x,y) is a probability distribution, it fullfits such condition. – Francisco Javier Delgado Ceped Aug 10 at 18:44 add a comment  |  up vote 10 down vote favorite 6 What is the simplest proof that mutual information is always non-negative? i.e., $I(X;Y)ge0$ probability-theory share | cite | improve this question edited Jun 17 ...
閱讀完整內容

Why am i infinitely getting the same tweet with the Twitter Search API?

圖片
up vote 0 down vote favorite My code workes perfectly for other queries, but for one query I am infinitely getting the same tweet. I can't understand what the problem is. API call: query='Allergic asthma OR Nonallergic asthma OR Occupational asthma OR EIB OR Exercise-induced bronchoconstriction OR Nocturnal asthma OR Cough-variant asthma' new_tweets = api.search(q=searchQuery, count=100, since_id=since_id, lang='en',tweet_mode='extended') if not new_tweets: print("No more tweets found") break for tweet in new_tweets: if True: print(jsonpickle.encode(tweet._json, unpicklable=False)) my_file = open('searchTweets.txt','a') my_file.write(jsonpickle.encode(tweet._json, unpicklable=False)+'n') my_file.close() else: continue Output in file: https://pastebin.com/JmRCsTeE These are the JSONs of the same Tweet. Other queries that work: Breast cancer OR Prostate cancer OR Colon cancer OR Lung cancer Type 2 dia...
閱讀完整內容