Time Limit - SQL Server 2008

Navigation
0

Good afternoon,

I would like to do a SQL to control the limit of the last hit of the employees point 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, I would get the value of the last strike of day 22 minus the first of day 23 and if the difference is less than 11 hours the value appears, and so on, in 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
    
asked by anonymous 30.06.2015 / 20:23

1 answer

0

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

You can check the SQLFiddle .

This will return employees who have less than 11:00 of difference between the time of exit 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 some explanation, or adjust the query, let me know!

    
30.06.2015 / 22:07
Problem with session variable Local e-mail server with graphical interface