Wednesday 8 July 2015

Swipe Details-Medium 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?

Assumption:There can be consecutive IN or OUT.

SELECT * FROM SWIPE;

















Outside Campus:
SELECT * FROM SWIPE
WHERE(BADGEID,SWIPE_TS)
IN
(
SELECT BADGEID,MAX(SWIPE_TS)
FROM SWIPE
GROUP BY BADGEID
)

AND ACTN='OUT'







Inside Campus:
SELECT * FROM SWIPE
WHERE(BADGEID,SWIPE_TS)
IN
(
SELECT BADGEID,MAX(SWIPE_TS)
FROM SWIPE
GROUP BY BADGEID
)

AND ACTN='IN'















No comments:

Post a Comment