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

































































-

這個網誌中的熱門文章

Is there any way to eliminate the singular point to solve this integral by hand or by approximations?

圖片
Clash Royale CLAN TAG #URR8PPP up vote 0 down vote favorite 1 $$int_0^1 (1+(x^-p-1)^1-p)^frac1pdx, pin mathbbR, pge 1$$ I find that $x=0,1$ are singular points(discontinuous points of this integral), is there a way to eliminate those singular points to make sure that this integral can be calculated by hand or by Taylor approximations. In either cases, what are those steps of calculating the integral? calculus real-analysis analysis approximation share | cite | improve this question edited Sep 11 at 5:07 asked Sep 11 at 4:48 Mclalalala 126 8 1 What is $p$? Please give more information to make it complete. – xbh Sep 11 at 5:02 @xbh already fixed it, thanks for reminding. – Mclalalala Sep 11 at 5:10 Seems that $0$ is not singular here… [I might compute wrong] – xbh Sep 11 at 5:30 @xbh can you show me how to get the close formula for the integral above? – Mclalalala Sep 11 at 5:33
閱讀完整內容

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
閱讀完整內容

Solve: $(3xy-2ay^2)dx+(x^2-2axy)dy=0$

圖片
Clash Royale CLAN TAG #URR8PPP up vote 0 down vote favorite Solve: $(3xy-2ay^2)dx+(x^2-2axy)dy=0$ The given differential equation is not exact. The given differential equation is homogeneous. Therefore, $I.F=frac1Mx+Ny$ $=frac1(3xy-2ay^2)x+(x^2-2axy)y$ $=frac1xy(4x-4ay)$ With Integrating Factor of this form (ugly value), it is difficult to compute further integration, that is involved in solving the equation. So, can we not solve the equation using the rule: If in the differential eqn $Mdx+Ndy=0$, provided $ Mx+Ny ne 0$, andM and N ar eboth homogeneous then $I.F=frac1Mx+Ny$ differential-equations share | cite | improve this question edited Aug 8 at 9:19 asked Aug 8 at 9:13 Soumee 626 2 13 put $y=vx,$ ............. – Magneto Aug 8 at 9:44 add a comment  |  up vote 0 down vote favorite Solve: $(3xy-2ay^2)dx+(x^2-2axy)dy=0$ The given differential equation is not exact. The given differential eq
閱讀完整內容