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.