go to next row when a column contain certain value

Question

This is an example of the table that i'm working with (24hour system)

|ID      |Name        |Department  |InOutID|Date      | Hours | Minutes|
________________________________________________________________________
0123     | Phil Jones | security   |1      |2018-07-07| 7      |30      
________________________________________________________________________
0123     | Phil Jones | security   |2      |2018-07-07| 10     |30      
_________________________________________________________________________
0123     | Phil Jones | security   |1      |2018-07-07| 13     |30 
_________________________________________________________________________
0123     | Phil Jones | security   |2      |2018-07-07| 17     |00          

i'm working with a scoring system database so basically what i need to do is verify the IdInOut column if it has 1 then its an entry if its 2 then its a exit and i should calculate the real amount of time that the epmloyee has spent in the office( the table is ordered by Date,Hours,Minutes desc) normally IdInOUt should be like this

    |IDInOut|
    _________
    2
    _________
    1
    _________
    2
    _________
    1  
    .....

basically what i need to do is set a cursor pointing at the first row and another cursor pointing at the second row and for every iteration i subsrtact hours and minutes of the second cursor from the first cursor and store them into a variable then use it afterward SO FAR SO GOOD but there is a problem with this previous table it's the following:

 |ID      |Name        |Department  |InOutID|Date      | Hours | Minutes|
   ________________________________________________________________________
   0123     | Phil Jones | security   |1      |2018-07-07|7      |30      
   ________________________________________________________________________
   0123     | Phil Jones | security   |1      |2018-07-07|7      |32     
   _________________________________________________________________________
   0123     | Phil Jones | security   |2      |2018-07-07|10     |30      
   _________________________________________________________________________
   0123     | Phil Jones | security   |1      |2018-07-07|13     |30 
   _________________________________________________________________________
   0123     | Phil Jones | security   |2      |2018-07-07|17     |00          

there are successive rows with the same IdInOut which represents a problem since my method of work is that with every iteration i mentioned previously every cursor jumps with 2 row but when this case occurs every cursor should jump with only one row in which case i didn't find the solution :/ i Know the explanation is pretty long but i'm really stuck here any help is welcomed

Answer 1

There is a relatively efficient way to determine if the data is correct -- well almost. It will determine if there are missing "1"s or "2"s in the data. This version does not determine if the first id is "1", because your question does not mention that.

The best solution is to use lag() and lead(). However, SQL Server 2008 doesn't support these functions. So, let's approach this as a gap-and-islands. Are there any situations with more than 1 "1" or "2" in a row for a given id? The following code should find these anomalies:

select id, inoutid, count(*) as num_in_row,
from (select t.*,
             row_umber() over (partition by id, inoutid order by date, hours, minute) as seqnum_ii,
             row_umber() over (partition by id order by date, hours, minute) as seqnum_i
      from t
     ) t
group by id, inoutid, (seqnum_i - seqnum_ii)
having count(*) > 1