Compare difference between values of the same column, having to disregard the first and the last of the day

Asked

Viewed 46 times

0

I’ve been racking my brain for a few days I need to do a query that brings up employee time records, but I have to ignore the first and last record of the day. In the example I would have to ignore lines 1 and 10 and compare the time difference of lines 2 and 3, 4 and 5, 6 and 7 and 8 and 9. I thank you for your attention.

My query is like this: 

select 
  f.nm_funcionario_nome Nome,
  r.tm_check
from tbl_funcionario f, tbl_registro r
where r.nm_registro = f.nr_cracha
order by 1,2

The result is: 

Nome    ..................   tm_check
Carlos Kayque A da S    02/05/2019 11:04:40

Carlos Kayque A da S    02/05/2019 11:12:27

Carlos Kayque A da S    02/05/2019 11:13:44

Carlos Kayque A da S    02/05/2019 11:45:01

Carlos Kayque A da S    02/05/2019 12:47:54

Carlos Kayque A da S    02/05/2019 12:54:39

Carlos Kayque A da S    02/05/2019 13:57:32

Carlos Kayque A da S    02/05/2019 13:58:04

Carlos Kayque A da S    02/05/2019 13:58:30

Carlos Kayque A da S    02/05/2019 14:31:35

Carlos Kayque A da S    03/05/2019 07:58:52

Carlos Kayque A da S    03/05/2019 08:11:03
mysql

1 answer

0

Try to eliminate these two using sub-selects:

select 
  f.nm_funcionario_nome Nome,
  r.tm_check
from tbl_funcionario f, tbl_registro r
where r.nm_registro = f.nr_cracha
and (r.tm_check > (select min(r1.tm_check) from tbl_registro r1 where r1.nr_cracha = r.nr_cracha)
and (r.tm_check < (select max(r2.tm_check) from tbl_registro r2 where r2.nr_cracha = r.nr_cracha)
order by 1,2

You may also need to consider the date.

  • I had to make some adjustments, but it still didn’t change the result. Brought the same information. &#xA;&#xA;&#xA;select&#xA; f.nm_funcionario_nome Nome,&#xA; r.tm_check&#xA;from tbl_funcionario f, tbl_registro r&#xA;where r.nm_registro = f.nr_cracha&#xA;and (r.tm_check > (select min(r1.tm_check) from tbl_registro r1 wherer 1.nm_registro = r1.nm_registro))&#xA;and (r.tm_check < (select max(R2.tm_check) from tbl_record R2 Where R2.nm_record = R2.nm_record)) order by 1,2

  • Note that in determining the maximum and minimum you referenced the same instance of the tbl_record table when the WHERE clause should compare the most internal SELECT instance with the most external instance. (r. tm_check > (select min(R1.tm_check) from tbl_record R1 Where R1.nm_record = r.nm_record)) and (r. tm_check < (select max(R2.tm_check) from tbl_record R2 Where R2.nm_record = r.nm_record))

  • This part worked thanks as I changed the field because it was not the badge but the record in the table ended up leaving the reference to own sub-query. I wonder if I can do this time comparison I’ll exemplify in excel. Name tm_check Result Carlos Kayque A of S 02/05/19 11:04 Carlos Kayque A of S 02/05/19 11:12 00:07:47 Carlos Kayque A da S 02/05/19 11:13 00:01:17 Carlos Kayque A da S 02/05/19 11:45 00:31:17 Carlos Kayque A of S 02/05/19 12:47 01:02:53

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.