victoriaharry
asked on
sql query to count records where field changed to specific value from another
Hi experts,
I created a mock table below to simulate the problem I'm trying to solve. I'm trying to write a query which would count all the DefectIDs where the status changed from Open to closed such as ID 125 in the table below. The count shouldn't included any other defects where the status changed to closed but the previous status wasn't Open. Using the example below the Defect 245 wouldn't be included in the results. I will also need a query to work in both oracle & SQL server databases in case the queries are slightly differebt.
Any assistance or ideas would be appreciated
DefectID Column2 Column 3 Status DateStamp
-------------------------- ---------- ---------- ---------- ---------- -
245 aaaaa bbbbbb New 2/10/2014 13:15:58
125 dgsdg dsgsgg New 2/10/2014 13:16:33
333 sdsdg gsdggs New 2/10/2014 13:16:49
125 fsafaf safafff Open 2/10/2014 15:20:44
245 fafasf fhfdhd Assigned 2/10/2014 15:25:12
125 asfasf safasff Closed 3/10/2014 09:30:22
245 gdgdsg gdsgg Closed 4/10/2014 11:14:11
Thanks
I created a mock table below to simulate the problem I'm trying to solve. I'm trying to write a query which would count all the DefectIDs where the status changed from Open to closed such as ID 125 in the table below. The count shouldn't included any other defects where the status changed to closed but the previous status wasn't Open. Using the example below the Defect 245 wouldn't be included in the results. I will also need a query to work in both oracle & SQL server databases in case the queries are slightly differebt.
Any assistance or ideas would be appreciated
DefectID Column2 Column 3 Status DateStamp
--------------------------
245 aaaaa bbbbbb New 2/10/2014 13:15:58
125 dgsdg dsgsgg New 2/10/2014 13:16:33
333 sdsdg gsdggs New 2/10/2014 13:16:49
125 fsafaf safafff Open 2/10/2014 15:20:44
245 fafasf fhfdhd Assigned 2/10/2014 15:25:12
125 asfasf safasff Closed 3/10/2014 09:30:22
245 gdgdsg gdsgg Closed 4/10/2014 11:14:11
Thanks
Note: Lead() requires MS SQL 2012 or higher (might be an issue), and Oracle 10.2 (which shouldn't be an issue).
What about the simple code
Of course, you may use INNER JOIN instead of IN.
SELECT COUNT(*)
FROM YourTable
WHERE Status = 'Closed'
AND DefectID IN SELECT(DefectID FROM YourTable WHERE Status = 'Open')
Or do you have some defects which can be reopen after closing or another defects order "incompatibility"?Of course, you may use INNER JOIN instead of IN.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Then filter accordingly
Open in new window
You cannot filter directly on the LEAD() calculation so a nested query is used so the column alias (next_state) may be used.