Link to home
Start Free TrialLog in
Avatar of victoriaharry
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
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Use the LEAD() function to access the next status value (based on datestamp for the same defectid)
Then filter accordingly
SELECT
      d.*
FROM (
      SELECT
            defects.*
          , LEAD(status) OVER (PARTITION BY defectid ORDER BY datestamp ASC) AS next_state
      FROM defects
      ) d
WHERE status = 'Open'
      AND next_state = 'Closed'
ORDER BY
      d.defectid
;

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.
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
SELECT COUNT(*)
 FROM YourTable
WHERE Status = 'Closed'
   AND DefectID IN SELECT(DefectID FROM YourTable WHERE Status = 'Open')

Open in new window

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
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial