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:47:50.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:48:40.0000000      IN          
88       ZE     2014-05-28    12:16:19.0000000      OUT 

Means for the every occurence of DEVICETYPE the latest row will be taken

share|improve this question
1  
Which SQL did you try? What was wrong with it? –  Colin 't Hart Jun 4 at 8:15
add comment

1 Answer 1

up vote 2 down vote accepted

To get that every row need to know the value of the next one, but the LEAD function is not in SQLServer 2008, so an auto-join is used in his stead

With I AS (
  SELECT EMPLID, Name, PUNCHDATE, PUNCHTIME, DEVICETYPE
       , ID = Row_Number() OVER (PARTITION BY EMPLID 
                                 ORDER BY EMPLID, PUNCHDATE DESC, PUNCHTIME DESC)
  FROM   Table1
)
Select a.EMPLID, a.Name, a.PUNCHDATE, a.PUNCHTIME, a.DEVICETYPE
FROM   I a
       LEFT  JOIN I b ON a.EmplID = b.EmplID And b.ID = a.ID - 1
WHERE  a.DeviceType <> b.DeviceType 
   OR  a.ID = 1
ORDER BY a.EMPLID, a.PUNCHDATE, a.PUNCHTIME

SQLFiddle demo

The Row_Number is used to simplify the JOIN condition.

share|improve this answer
    
If we want a bit changed result as : from the devicetype IN the first occurrence will be taken and from the devicetype OUT the last occurrence will be taken, how should we will do? –  Khaksar Weqar Jun 18 at 7:11
    
@KhaksarWeqar that is a different question, and a more complex one, but if you create it I will take a look –  Serpiton Jun 18 at 7:53
    
I posted it seperatedly at here –  Khaksar Weqar Jun 18 at 9:12
add comment

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.