I have the follwoing result set from the table PUNCHREPORT
EMPLID Name PUNCHDATE PUNCHTIME DEVICETYPE
..................................................................
70 GT 2014-05-28 07:46:53.0000000 IN
70 GT 2014-05-28 07:46:58.0000000 IN
70 GT 2014-05-28 07:47:50.0000000 IN
70 GT 2014-05-28 12:16:19.0000000 OUT
70 GT 2014-05-28 12:17:55.0000000 OUT
70 GT 2014-05-28 12:24:27.0000000 IN
70 GT 2014-05-28 16:32:57.0000000 OUT
70 GT 2014-05-28 16:33:57.0000000 OUT
70 GT 2014-05-28 16:33:59.0000000 OUT
72 MS 2014-05-28 07:47:50.0000000 IN
72 MS 2014-05-28 12:16:19.0000000 OUT
88 ZE 2014-05-28 07:47:50.0000000 IN
88 ZE 2014-05-28 07:48:40.0000000 IN
88 ZE 2014-05-28 12:16:19.0000000 OUT
I need the above should be result as follow:
EMPLID Name PUNCHDATE PUNCHTIME DEVICETYPE
..................................................................
70 GT 2014-05-28 07:46:53.0000000 IN
70 GT 2014-05-28 12:17:55.0000000 OUT
70 GT 2014-05-28 12:24:27.0000000 IN
70 GT 2014-05-28 16:33:59.0000000 OUT
72 MS 2014-05-28 07:47:50.0000000 IN
72 MS 2014-05-28 12:16:19.0000000 OUT
88 ZE 2014-05-28 07:47:50.0000000 IN
88 ZE 2014-05-28 12:16:19.0000000 OUT
Means for every occurence of DEVICETYPE
IN
the first row will be taken and for OUT
the last row will be taken.
Best Answer
Doing that is tricky, because you need to check the differences between the data in both orders, ascending to get the first 'IN', descending to get the last 'OUT'.
SQLFiddle
the
S
CTE
is to remove theCASE
from theWHERE
condition, in theCTE
I left a lot of noise data, that can be useful to understand theCASE
conditions