Select Electronic Point Record (SQL)

Question

Select Electronic Point Record (SQL)

Navigation

#1 by (1 votes)

3

How can I collect only the first and last mark, where an official can have X Markings and even markings 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 it?

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: Remember that on some occasions the TM is not filled with the information of 1st and / or 2nd (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

link

Any questions just ask ...

sql sql-server query

asked by anonymous 01.09.2018 / 21:35

1 answer

Access a list / array in a JSON URL Java Spring Boot - Read a file from the Resources folder inside a .jar

score 1 · Accepted Answer

For 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 the columns pDATA and pDTAPO always have 8 characters, I suggest that you declare them as char (8).

If you need 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 there is a break (eg deadlock or vice versa), as the result may be wrong (or not) for that day.

Given the complementary information that a shift can start in one day and finish in another, here's the 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;

Just as in code # 2, the reliability of the result is reduced for cases where a break occurs (for example, deadlock 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.