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