Wednesday 8 July 2015

Swipe Details-Complex scenario

I have a table where Swipe IN ans Swipe OUT details are recorded.Suppose,If I need to find
How many employees are inside/outside the campus at any given point of time in a given day?

Assumption:There can be consecutive IN or OUT

SELECT * FROM SWIPE;
















08-JUL-15 04.40.38 AM -----Point of time


SELECT ACTN,COUNT(*) FROM SWIPE
WHERE(BADGEID,SWIPE_TS)
IN
(
SELECT BADGEID,MAX(SWIPE_TS)
FROM SWIPE
WHERE SWIPE_TS<='08-JUL-15 04.40.38 AM'
GROUP BY BADGEID
)
GROUP BY ACTN







No comments:

Post a Comment