Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 0 down vote accepted

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

With I AS (
  SELECT EMPLID, Name, PUNCHDATE, PUNCHTIME, DEVICETYPE
       , ID_Up = Row_Number() OVER (PARTITION BY EMPLID 
                                    ORDER BY EMPLID, PUNCHDATE, PUNCHTIME)
       , ID_Down = Row_Number() OVER (PARTITION BY EMPLID 
                                      ORDER BY EMPLID, PUNCHDATE DESC, PUNCHTIME DESC)
  FROM   Table1
), S AS (
  SELECT a.EMPLID, a.Name, a.PUNCHDATE, a.PUNCHTIME, a.DEVICETYPE
       , a.ID_Up aUID, b.ID_Up bUID, a.ID_Down aDID, c.ID_Down cDID
       , a.DeviceType aDeviceType, b.DeviceType bDeviceType, c.DeviceType cDeviceType
       , OKValues 
       = CASE WHEN a.ID_Up = 1 THEN 1
              WHEN a.DeviceType = 'IN' AND b.DeviceType = 'OUT' THEN 1
              WHEN a.ID_Down = 1 THEN 1
              WHEN a.DeviceType = 'OUT' AND c.DeviceType = 'IN' THEN 1
              ELSE 0
         END
  FROM   I a
         LEFT  JOIN I b ON a.EmplID = b.EmplID And b.ID_Up = a.ID_Up - 1
         LEFT  JOIN I c ON a.EmplID = c.EmplID And c.ID_Down = a.ID_Down - 1
)
SELECT EMPLID, Name, PUNCHDATE, PUNCHTIME, DEVICETYPE
FROM   S
WHERE  OKValues = 1
ORDER BY EMPLID, PUNCHDATE, PUNCHTIME

SQLFiddle

the S CTE is to remove the CASE from the WHERE condition, in the CTE I left a lot of noise data, that can be useful to understand the CASE conditions

share|improve this answer

try this

;WITH cte AS (
  SELECT
    t.EmpID,
    t.Name,
    t.PunchDate,
    t.PunchTime,
    t.DeviceType,
    ROW_NUMBER() OVER (PARTITION BY t.EmpID ORDER BY t.PunchDate, t.PunchTime) rw
    FROM test.dbo.Table_1 t  
  )


  ,cte2 AS (
  SELECT
    t.EmpID,
    t.Name,
    t.PunchDate InPunchDate,
    t.PunchTime InPunchTime,
    t2.PunchDate OutPunchDate,
    t2.PunchTime OutPunchTime
  FROM cte t
  JOIN cte t2 ON t.EmpID = t2.EmpID AND t.rw = t2.rw-1 AND t.DeviceType != t2.DeviceType 
  WHERE t.DeviceType != 'OUT'
  )

  SELECT * FROM (
    SELECT
        EmpID,
        Name,
        InPunchDate PunchDate,
        InPunchTime PunchTime,
        'IN' Device
        from cte2
    UNION ALL
    SELECT
        EmpID,
        Name,
        OutPunchDate PunchDate,
        OutPunchTime PunchTime,
        'OUT' Device
    FROM cte2
  ) AS t1
  ORDER BY EmpID, PunchDate, PunchTime
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.