Select Electronic Point Record (SQL)

Question:

How can I gather only the first and last appointment, where an employee can have X Appointments and even appointments the next day?

According to the example below

+------+--------+-------+-------+
| FUNC |  DATA  |ENTRADA| SAIDA |
+------+--------+-------+-------+
|000001|20180901| 07.20 | 17.20 |
|000001|20180902| 07.20 | 17.20 |
|000001|20180903| 07.20 | 17.20 |
|000001|20180904| 07.20 | 01.00 |
|000001|20180905| 07.20 | 16.00 |
|000001|20180906| 05.00 | 17.00 |
+------+--------+-------+-------+

Note: the information I intend to obtain is this, is it from ?

Data

Text Data

+------+--------+-----+----+--------+
| FUNC |  DATA  |HORA | TM |DATAAPO |
+------+--------+-----+----+--------+
|000001|20180901|07.20| 1E |20180901|
|000001|20180901|17.20| 1S |20180901|
|000001|20180902|07.20| 1E |20180902|
|000001|20180902|12.00| 1S |20180902|
|000001|20180902|13.00|    |20180902|
|000001|20180902|17.20|    |20180902|
|000001|20180903|07.20| 1E |20180903|
|000001|20180903|12.00| 1S |20180903|
|000001|20180903|13.00| 2E |20180903|
|000001|20180903|17.20|    |20180903|
|000001|20180904|07.20|    |20180904|
|000001|20180905|01.00| 1S |20180904|
|000001|20180905|07.20| 1E |20180905|
|000001|20180905|16.00|    |20180905|
|000001|20180906|05.00|    |20180906|
|000001|20180906|12.00| 1S |20180906|
|000001|20180906|13.00|    |20180906|
|000001|20180906|17.00| 2S |20180906|
+------+--------+-----+----+--------+

Note: Remembering that on some occasions the TM is not filled with the 1st and/or 2nd information (Exit or Entry)

Data in SQL Format

CREATE TABLE FUNC (
 pFUNC varchar(6),
 pNOME varchar(100)
 );

insert into FUNC (pFUNC, pNOME) values
('000001','FULANO'),
('000002','BELTRANO'),
('000003','SICLANO')

CREATE TABLE PONTO (
 pFUNC varchar(6),
 pDATA varchar(8),
 pHORA float,
 pTM   varchar(2),
 pDTAPO varchar(8)
 );

insert into PONTO (pFUNC, pDATA, pHORA, pTM, pDTAPO) values
('000001','20180901', 7.20,'1E','20180901'),
('000001','20180901',17.20,'1S','20180901'),
('000001','20180902', 7.20,'1E','20180902'),
('000001','20180902',12.00,'1S','20180902'),
('000001','20180902',13.00,'  ','20180902'),
('000001','20180902',17.20,'  ','20180902'),
('000001','20180903', 7.20,'1E','20180903'),
('000001','20180903',12.00,'1S','20180903'),
('000001','20180903',13.00,'2E','20180903'),
('000001','20180903',17.20,'  ','20180903'),
('000001','20180904', 7.20,'  ','20180904'),
('000001','20180905', 1.00,'1S','20180904'),
('000001','20180905', 7.20,'1E','20180905'),
('000001','20180905',16.00,'  ','20180905'),
('000001','20180906', 5.00,'  ','20180906'),
('000001','20180906',12.00,'1S','20180906'),
('000001','20180906',13.00,'  ','20180906'),
('000001','20180906',17.00,'2S','20180906')

Data in SQLFiddle

http://sqlfiddle.com/#!18/f9f25/2

Any questions just ask…

Answer:

From the desired result, it seems to me that the date column to be used is pDTAPO. Here is a suggestion for evaluating:

-- código #1
SELECT pFUNC, pDTAPO, 
       min(case when right(pTM,1) = 'E' then pHORA end) as ENTRADA,
       max(case when right(pTM,1) = 'S' then pHORA end) as SAIDA
  from PONTO
  group by pFUNC, pDTAPO;

If pDATA and pDTAPO columns will always have 8 characters, I suggest that you declare them as char(8).


If it is necessary to ignore the pTM column, we have:

-- código #2
SELECT pFUNC, pDTAPO, 
       min(pHORA) as ENTRADA,
       max(pHORA) as SAIDA
  from PONTO
  group by pFUNC, pDTAPO;

However, the reliability of the result is reduced for cases where a break occurs (for example, entry without exit or vice versa), as the result may be wrong (or not) for that day.


Considering the complementary information that a shift can start in one day and end in another, here is a suggestion:

-- código #3
with pontoDataHora as (
SELECT pFUNC, pDTAPO, 
       datetimefromparts(left(pDATA, 4), 
                         substring(pDATA, 5, 2),
                         substring(pDATA, 7, 2),
                         round(pHORA, 0, 1),
                         cast((pHORA * 100) as int) % 100,
                         0, 0) as pDATAHORA
  from PONTO
) 
SELECT pFUNC, pDTAPO, 
       min(pDATAHORA) as ENTRADA,
       max(pDATAHORA) as SAIDA
  from pontoDataHora
  group by pFUNC, pDTAPO;

As with code #2, the reliability of the result is reduced for cases where a break occurs (eg, dead-end input or vice versa), as the result may be wrong (or not) for that day. The error is not in the code but in the data.

Scroll to Top