none
Get Two rows only

    Question

  • Hi,

    I have the following script.

    DECLARE @T TABLE (AssociateID int,
    				  TiaId int,
    				  SumHrs int,
    				  CorrectHrs int,
    				  ClockTime datetime,
    				  HourType varchar(10))
    Insert @T values (1,2,521,491,'2013-07-08 21:04:00.000','ClockOut'),
    				 (1,2,521,491,'2013-07-08 17:29:00.000','ClockIn'),
    				 (1,2,521,491,'2013-07-08 16:22:00.000','ClockOut'),
    				 (1,2,521,491,'2013-07-08 11:16:00.000','ClockIn'),
    				 
    				 (2,5,NULL,NULL,'2013-07-08 20:20:00.000','ClockIn'),
    				 (2,5,NULL,NULL,'2013-07-08 17:00:00.000','ClockOut'),
    				 (2,5,NULL,NULL,'2013-07-08 11:53:00.000','ClockIn'),
    				 
    				 (3,8,479,449,'2013-07-08 16:30:00.000','ClockOut'),
    				 (3,8,479,449,'2013-07-08 16:00:00.000','ClockIn')
    				 
    select * from @T

    From this I want to get the rows that are in pairs of ClockOut-ClockIn. The output should be like this:


    Thanks, hsbal

    Wednesday, July 10, 2013 10:52 PM

Answers

  • ;with mycte as
    (select *, row_number() Over(Partition by tiaid order by ClockTime DESC) rn  from @T)
    Select * from mycte 
    WHERE rn>1 or (rn= 1 AND HourType='ClockOut')
    order by associateid,ClockTime desc

    • Marked as answer by Harry Bal Thursday, July 11, 2013 4:55 PM
    Thursday, July 11, 2013 4:17 PM

All replies

  • The expected output you've provided is precisely the same as the SELECT statement in your sample code except there is a single row missing. 

    Are you sure the expected output is what you want ?


    Thanks! Josh

    Wednesday, July 10, 2013 11:56 PM
  • Hi Josh,

    yes, I am looking for code that provides me the expected output.

     


    Thanks, hsbal

    Thursday, July 11, 2013 12:27 AM
  • In such a case you need to provide the Original data not just the expected output.
    Thursday, July 11, 2013 12:57 AM
  • The original data is same like I have shown in table variable. Please note that my table variable select * is NOT same as expected output. The expected output has clock out - clock in  pairs with clock time in desc order. where as table variable has one extra row 
     (2,5,NULL,NULL,'2013-07-08 20:20:00.000','ClockIn')
    which is not required as it has missed clock out...

    Thanks, hsbal

    Thursday, July 11, 2013 2:56 AM
  • Is this what you are looking for ?

    SELECT AssociateID,TiaId,SumHrs,CorrectHrs,ClockTime,HourType FROM (
    	SELECT *,CASE WHEN RNK=1 AND HourType='ClockIn' THEN 'Y' ELSE 'N' END AS [Delete] FROM (
    		SELECT *,ROW_NUMBER() OVER(PARTITION BY AssociateID,TiaID ORDER BY ClockTime DESC) AS RNK FROM @T 
    	) X
    ) Y
    WHERE [Delete]='N'


    Regards, RSingh

    Thursday, July 11, 2013 4:39 AM
  • Hi,

    This is not correct. It is gives the same output as we do select * from @T.


    Thanks, hsbal

    Thursday, July 11, 2013 1:40 PM

  • select * from
    (
    select *,
    count (*) over (partition by AssociateID,SumHrs) cnt,
    row_number() over (partition by AssociateID,SumHrs order by AssociateID) rn from @T
    ) as der where cnt %2=0  or rn<3

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, July 11, 2013 1:55 PM
  • Hi Uri,

    Thanks for your reply. I think I was not clear with with my ques. The following code explains it with more clarity

    DECLARE @T TABLE (AssociateID int,
    				  TiaId int,
    				  SumHrs int,
    				  CorrectHrs int,
    				  ClockTime datetime,
    				  HourType varchar(10))
    Insert @T values 
    				(1,2,521,491,'2013-07-08 17:29:00.000','ClockIn'),
    				(1,2,521,491,'2013-07-08 21:04:00.000','ClockOut'),
    				(1,2,521,491,'2013-07-08 16:22:00.000','ClockOut'),
    				 (1,2,521,491,'2013-07-08 11:16:00.000','ClockIn'),
    				 
    				 (2,5,NULL,NULL,'2013-07-08 20:20:00.000','ClockIn'),
    				 (2,5,NULL,NULL,'2013-07-08 17:00:00.000','ClockOut'),
    				 (2,5,NULL,NULL,'2013-07-08 11:53:00.000','ClockIn'),
    				 
    				 (3,8,479,449,'2013-07-08 16:30:00.000','ClockOut'),
    				 (3,8,479,449,'2013-07-08 16:00:00.000','ClockIn'),
    				 
    				 (4,21,345,234,'2013-07-08 13:52:01.412','ClockIn'),
    				 (4,21,345,234,'2013-07-08 15:45:13.765','ClockOut'),
    				 (4,21,345,234,'2013-07-08 16:11:32.115','ClockIn')
    				 
    select * from @T
    select * from @T
    order by associateid,ClockTime desc

    I want output to be like this statment
    select * from @T
    order by associateid,ClockTime desc

    BUT row 5 and 10 being omitted, since they do not have Clock-Out to match (when clock time is desc

    Thanks, hsbal


    Note: the number of entries for each associateID may be any. For example they can have any even or odd number of occurences. In my example, I just shown AssociateID=4 , 3 times only. It may be 7 or 6. If it will be 6 then it will have clock out-clock in pair.
    • Edited by Harry Bal Thursday, July 11, 2013 4:03 PM Updated
    Thursday, July 11, 2013 3:59 PM
  • ;with mycte as
    (select *, row_number() Over(Partition by tiaid order by ClockTime DESC) rn  from @T)
    Select * from mycte 
    WHERE rn>1 or (rn= 1 AND HourType='ClockOut')
    order by associateid,ClockTime desc

    • Marked as answer by Harry Bal Thursday, July 11, 2013 4:55 PM
    Thursday, July 11, 2013 4:17 PM