Complex SQL query (at least for me)

Question

I am trying to develop a sql query that will return a list of serial numbers. The table sets that whenever the serial number reaches the step, the date and time are entered. At the end of this step, another date and time is entered. I want to develop a query that will give me a list of serial numbers that went into this step, but did not go out of this step. They can be entered several times, so I am only looking for serial numbers that do not have outputs after and enter.

Example (for convenience, call the table "Table 1")

 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

In the above table, you need to get serial numbers 1 and 2, but 3 should not be.

Can this be done in a subscription request with subqueries?

Answer 1

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

'enter', enter "exit" ( )

Answer 2

, , . . , . , serialNO? , , , , (, coupld ). , , . ( ), , .

Answer 3

'enter', 'exit'. , .

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 4

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

, Date , .

Answer 5

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 6

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 7

, , , , "enter" "".

Answer 8

MS SQL 2005 2008, CTE , ...

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

, ...

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 9

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"

, exits, enters

Answer 10

, , . , . "" ( "enter" "exit", ), , (, enter/process/exit) - ALTER TABLE .

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

:

SELECT serial,enter_date FROM table1 WHERE exit_date IS NULL;

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

Answer 11

Here is a simple query that should work with your script

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

I tested this and it will give you lines with serial numbers 1 and 2