Complex SQL Query (at least for me)

Question

I'm trying to develop a sql query that will return a list of serial numbers. The table is set up that whenever a serial number reaches a step, the date and time are entered. When it completes the step, another date and time are entered. I want to develop a query that will give me the list of serial numbers that have entered the step, but not exitted the step. They may enter more than once, so I'm only looking for serial numbers that don't have exits after and enter.

Ex.(for easy of use, call the table "Table1")

 1. Serial | Step  | Date

 2. 1      | enter | 10/1
 3. 1      | exit  | 10/2
 4. 1      | enter | 10/4
 5. 2      | enter | 10/4
 6. 3      | enter | 10/5
 7. 3      | exit  | 10/6

For the above table, serial numbers 1 and 2 should be retrieved, but 3 should not.

Can this be done in a signle query with sub queries?

Answer 1

SELECT DISTINCT Serial
FROM Table t
WHERE (SELECT COUNT(*) FROM Table t2 WHERE t.Serial = t2.Serial AND Step = 'exit') <
      (SELECT COUNT(*) FROM Table t2 WHERE t.Serial = t2.Serial AND Step = 'enter')

Answer 2

This will give you all 'enter' records that don't have an ending 'exit'. If you only want a list of serial numbers you should then also group by serial number and select only that column.

SELECT t1.* 
FROM Table1 t1
LEFT JOIN Table1 t2 ON t2.Serial=t1.Serial 
    AND t2.Step='Exit' AND t2.[Date] >= t1.[Date]
WHERE t1.Step='Enter' AND t2.Serial IS NULL

Answer 3

If you're sure that you've got matching enter and exit values for the the ones you don't want, you could look for all the serial values where the count of "enter" is not equal to the count of "exit".

Answer 4

select * from Table1 
group by Step 
having count(*) % 2 = 1

this is when there cannot be two 'enter' but each enter is followed by an 'exit' (as in the example provided)

Answer 5

SELECT * FROM Table1 T1
WHERE NOT EXISTS (
  SELECT * FROM Table1 T2   
  WHERE T2.Serial = T1.Serial 
    AND T2.Step = 'exit'
    AND T2.Date > T1.Date
)

Answer 6

If you're using MS SQL 2005 or 2008, you could use a CTE to get the results you're looking for...

WITH ExitCTE
AS
    (SELECT Serial, StepDate
    FROM #Table1
    WHERE Step = 'exit')
SELECT  A.*
FROM    #Table1 A LEFT JOIN ExitCTE B ON A.Serial = B.Serial AND B.StepDate > A.StepDate
WHERE   A.Step = 'enter'
     AND B.Serial IS NULL

If you're not using those, i'd try for a subquery instead...

SELECT  A.*
FROM    #Table1 A LEFT JOIN (SELECT Serial, StepDate
          FROM #Table1
          WHERE Step = 'exit') B 
      ON A.Serial = B.Serial AND B.StepDate > A.StepDate
WHERE   A.Step = 'enter'
     AND B.Serial IS NULL

Answer 7

Personally I think this is something best done through a change in the way the data is stored. The current method cannot be efficient or effective. Yes you can mess around and find a way to get the data out. However, what happens when you have multiple entered steps with no exit for the same serialNO? Yeah it shouldn't happen but sooner or later it will unless you have code written to prevent it (code which coupld get complicated to write). It would be cleaner to have a table that stores both the enter and exit in the same record. Then it become trivial to query (and much faster) in order to find those entered but not exited.

Answer 8

In Oracle:

SELECT *
FROM (
 SELECT serial,
 CASE
   WHEN so < 0 THEN "Stack overflow"
   WHEN depth > 0 THEN "In"
   ELSE "Out"
 END AS stack
 FROM (
  SELECT serial, MIN(SUM(DECODE(step, "enter", 1, "exit", -1) OVER (PARTITION BY serial ORDER BY date)) AS so, SUM(DECODE(step, "enter", 1, "exit", -1)) AS depth
  FROM Table 1
  GROUP BY serial
 )
)
WHERE stack = "Out"

This will select what you want AND filter out exits that happened without enters

Answer 9

Several people have suggested rearranging your data, but I don't see any examples, so I'll take a crack at it. This is a partially-denormalized variant of the same table you've described. It should work well with a limited number of "steps" (this example only takes into account "enter" and "exit", but it could be easily expanded), but its greatest weakness is that adding additional steps after populating the table (say, enter/process/exit) is expensive — you have to ALTER TABLE to do so.

serial  enter_date  exit_date
------  ----------  ---------
     1        10/1       10/2
     1        10/4       NULL
     2        10/4       NULL
     3        10/5       10/6

Your query then becomes quite simple:

SELECT serial,enter_date FROM table1 WHERE exit_date IS NULL;

serial  enter_date
------  ----------
     1        10/4
     2        10/4

Answer 10

Here's a simple query that should work with your scenario

SELECT Serial FROM Table1 t1
WHERE Step='enter' 
AND (SELECT Max(Date) FROM Table1 t2 WHERE t2.Serial = t1.Serial) = t1.Date

I've tested this one and this will give you the rows with Serial numbers of 1 & 2

Answer 11

I tested this in MySQL.

SELECT Serial, 
  COUNT(NULLIF(Step,'enter')) AS exits, 
  COUNT(NULLIF(Step,'exit')) AS enters 
FROM Table1
  WHERE Step IN ('enter','exit')
GROUP BY Serial
HAVING enters <> exits

I wasn't sure what the importance of Date was here, but the above could easily be modified to incorporate intraday or across-days requirements.