Select Electronic Point Record (SQL)

Question

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

As 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 want to get is this, will the ?

Dice

Data in Text

+------+--------+-----+----+--------+
| 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 in some occasions the TM is not filled with the information of 1º and/ or 2º (Output or Input)

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 doubt just ask...

Answer 1

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

-- 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 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 break occurs (for example, input without exit or vice versa), because the result may be wrong (or not) for that day.

---

Considering the additional information that one 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 in code #2, the reliability of the result is reduced for cases where break occurs (for example, dead-end entry or vice versa), because the result may be wrong (or not) for that day. The error is not in the code but in the data.