Complex SQL Query (at least for me)

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?

From stackoverflow

• 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')
  

• 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
  

• 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".

• 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)

  Joel Coehoorn : he specifically stated there could be multiple enters

  Learning : but never said anything about multiple enters without exits.

  Leon Tayson : This does not run at all!

  Cade Roux : You can't actually SELECT * with a GROUP BY. The original poster just meant it as shorthand for SELECT [Step] FROM Table1 GROUP BY [Step] HAVING COUNT(*) % 2 = 1. Also of note, the modulo operator (%) is not ANSI, but IS widely available.

  Ozan BAYRAM : what is the trick about "having count(*) % 2 = 1" can anyone explain?

• 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
  )
  

• 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
  

• 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.

  Russ Cam : my first thought was that it would probably be better to have one record with enter date and exit date (allowing null) fields. If it is the case that "each serial number can enter and exit multiple times" then you could have a table mapping multiple enter and exit records to a serial number.

• 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

• 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
  

• 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

• 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.