-- input parameter
var date1 varchar2(8);
exec :date1 := &1;

--  no headers
SET PAGESIZE 0; 
 
SELECT s.SERIALNUMBER,s."STATE",s."AT",s.OPERATORID FROM sample s
WHERE SUBSTR("AT",1,8) = :date1
AND SERIALNUMBER IN 
( SELECT SERIALNUMBER 
  FROM 
  ( SELECT t.SERIALNUMBER, Count(*) 
    FROM sample t
    WHERE SUBSTR(t."AT",1,8) = :date1
    GROUP BY t.SERIALNUMBER
    HAVING Count(*) > 1
  )
)
ORDER BY s.SERIALNUMBER,s."AT";

EXIT;