Journey Boundary - SQL Server 2008

Question

Good afternoon,

would like to make an SQL to control the limit of the last beat of the point of the employees with the first of the other day, filtering the amount of times the calculation gave less than 11 hours for example.

I have the data below, so for example, would take the value of the last beat of day 22 less the first of day 23 and if the difference is less than 11 hours the value appears, and so on, on consecutive dates, if the difference between dates is greater than 1 it ignores the calculation.

CHAPA       DATA        BATIDA

005770    | 22/04/2015  | 07:17  

005770    | 22/04/2015  | 11:39  

005770    | 22/04/2015  | 13:02  

005770    | 22/04/2015  | 20:23 

005770    | 23/04/2015  | 07:21  

005770    | 23/04/2015  | 12:59 

005770    | 23/04/2015  | 16:56 

005770    | 24/04/2015  | 07:31

005770    | 24/04/2015  | 11:35

005770    | 24/04/2015  | 13:00

005770    | 24/04/2015  | 17:33

Answer 1

Calculating the difference between the two dates reliably is not very easy, but you can do the following:

;with cte as (

  select chapa, 
         data, 
         min(cast(data as datetime) + cast(batida as datetime)) primeira_batida, 
         max(cast(data as datetime) + cast(batida as datetime)) ultima_batida, 
         row_number() over (partition by chapa order by data) rn
  from tbl_registos
  group by chapa, data

)  
select c1.chapa, 
       c1.data, 
       c1.ultima_batida, 
       c2.data, 
       c2.primeira_batida, 
       convert(varchar(3), datediff(mi, c1.ultima_batida, c2.primeira_batida) / 60) + ':' +
          right('0' + convert(varchar(2), datediff(mi, c1.ultima_batida, c2.primeira_batida) % 60), 2) diferenca_horas
from   cte c1
inner join cte c2
   on c2.chapa = c1.chapa
  and c2.rn = c1.rn + 1 
where convert(varchar(3), datediff(mi, c1.ultima_batida, c2.primeira_batida) /60) + ':' +
          right('0' + convert(varchar(2),datediff(mi, c1.ultima_batida, c2.primeira_batida) % 60), 2) 
          < '11:00'

For a table with the following data

CHAPA       DATA        BATIDA
005770    | 22/04/2015  | 07:17  
005770    | 22/04/2015  | 11:39  
005770    | 22/04/2015  | 13:02  
005770    | 22/04/2015  | 20:23 
005770    | 23/04/2015  | 07:21  
005770    | 23/04/2015  | 12:59 
005770    | 23/04/2015  | 16:56 
005770    | 24/04/2015  | 07:31
005770    | 24/04/2015  | 11:35
005770    | 24/04/2015  | 13:00
005770    | 24/04/2015  | 17:33

This will be the output:

chapa     | data        | ultima_batida | data         | primeira_batida | diferenca_horas
005770    | 2015-04-22  | 20:23:00      | 2015-04-23   | 07:21:00        | 10:58 

Can you check the Sqlfiddle.

This will return employees who have less than 11:00 difference between the time of departure and the time of entry (on two consecutive days). Notice that if the difference is 11:01 they will not be shown. I think this is what you want. If you need any explanation, or adjustment in the query, let us know!