how to looping, check every cells per records and comparing them and count the results?
-
hello, can someone help me with this SQL Query?
in "table Order" ClientID A1002 has put an order and get OrderID 5 (Rows 2) , and then he cancel and update the Order.
the system will create new OrderID 12 (Rows 3) and put 5 in column PrevOrderID (Rows 3) and also update NextOrderID in Rows 2 with value 12
and so on until Rows 4.how can i get results like this :
Only shows data that bigger than @count > 2 (2 x cancel and update the orders)
Date = 6/27/2014 8:31:25 AM (the first record he put on system and/or the first time he cancel and update the order)
ClientID = A1002
Products = Pencil
Count Cancel = 3 ( 3 times he change / update the orders ).i believe this SQL query needs temp table and Looping?
please advice how to get the result.
thank you
-
I don't understand you question. How are you determining that value of the variable @Count? Based on the vague information we have though, I doubt this needs either a temporary or a loop (loops are almost always a poor choice when you can do someone in a dataset method).
I would guess that what you need to use here with an aggregate function (probably COUNT) and the OVER clause. Have a look at https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017.
If you don't manage to get the answer you want, what results are you expecting for your query and what did you try?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Please provide DDL and some sample data. Based on what you are asking for results, does this work for you?
IF OBJECT_ID('tempdb..#T') IS NOT NULL DROP TABLE #Tcreate table #t(OrderID int, OrderDate datetime, ClientID varchar(5), Product varchar(10), PrevOrderID int, NextOrderID int)
insert into #t values
(5, '6/27/2014 08:31', 'A1002','Pencil',0,12),
(12, '6/27/2014 08:34', 'A1002','Pencil',5,22),
(22, '6/27/2014 09:01', 'A1002','Pencil',12,39),
(331, '6/27/2014 10:41', 'A1002','eraser',10,2334),
(2334, '6/24/2014 15:47', 'A1003','Pencil',331,2382),
(2344, '6/24/2014 15:47', 'A1002','Pencil',0,0)select ClientID, Product, min(orderDate) OrderDate, count(1) - 1 NumChanges
from #t
group by ClientID, Product
having count(1) - 1 > 0For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ - Mike01 - Monday, October 1, 2018 7:20 AM
this does not work for me.
so basiclly what i want to achieve is :
Query that will check how many times clientID Amend the order at the same day, per clientid, per product and how manytimes does he amend the order ( from the original order)something like this :
the query starts from rows 1.
if NextorderID != 0 then go to rows where OrderID = NextOrderID on the first row and so on until NextOrderID = 0 And Count how many times its checking
and then the result will be showed per day, per clientid, per product and number change (how many times its checks)
next it will check rows 2. firsts it will check is OrderID 2 is IN NextOrderID on the Above rows
If yes go check rows 3 and so on
if No back to first logic above ( if NextorderID != 0 then go to rows where OrderID = NextOrderID on the first row and so on until NextOrderID = 0 And Count how many times its checking). -
Update :
check from row 1 since nextid not 0, then go to row 4, since nextid on row 4 not 0 then go to row 11. since on row 11 nextID 16 then go to row 16. since on row 16 nextID = 0 then count how many time did he amend the orders ( 3 times ).go to next row where rows not mentioned on above "logic' which is go to row 2
and so on
basiclly what i want to achieve is how many times the customer Amend the order Based on original or the first ID using SQL 2008 R2 - gevali80 - Wednesday, October 3, 2018 4:40 AMUpdate :
check from row 1 since nextid not 0, then go to row 4, since nextid on row 4 not 0 then go to row 11. since on row 11 nextID 16 then go to row 16. since on row 16 nextID = 0 then count how many time did he amend the orders ( 3 times ).go to next row where rows not mentioned on above "logic' which is go to row 2
and so on
basiclly what i want to achieve is how many times the customer Amend the order Based on original or the first ID using SQL 2008 R2Images of data isn't helpful to other users, I'm afraid. Have a look at the link in my signature; it'll mean people are far more inclined to answer your question. Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply