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