Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql query to count records where field changed to specific value from another

Posted on 2014-10-04
4
Medium Priority
?
855 Views
Last Modified: 2014-10-19
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
0
Comment
Question by:victoriaharry
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40361228
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.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 40361266
Note: Lead() requires MS SQL 2012 or higher (might be an issue), and Oracle 10.2 (which shouldn't be an issue).
0
 
LVL 43

Expert Comment

by:pcelba
ID: 40361344
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.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40364213
;WITH data_with_row_nums AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY DefectID ORDER BY DateStamp) AS row_num
    FROM tablename
)
SELECT d1.DefectId, d1.Column2, d1.Column3, d1.Status, d1.DateStamp, d0.DateStamp AS DateStamp_Opened
FROM data_with_row_nums d1
INNER JOIN data_with_row_nums d0 ON
    d0.DefectID = d1.DefectID AND
    d0.row_num = d1.row_num - 1 AND
    d0.status = 'Open'
WHERE
    d1.Status = 'Closed'
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question