SQL Server Questions When Adding Shift 3 (SUM)

Question

How to make a sum (SUM) of shift 3 of a company and organize per day? The problem is that shift 3 ends the next day (it starts at 10 pm and ends at 6 pm the next day), so I am trying to make an exception only to shift 3 that on the current day disregards the values obtained before 6 am and disappear from 22 pm until 6 am the next day. Below is the script I already made and the result obtained In the result obtained today’s Shift 3 (day 14 is already with produced data, but this data should be from shift 3 yesterday)

SELECT 
STR(DAY(E3TimeStamp),2) + '/' + STR(MONTH(E3TimeStamp),1) + '/' + STR(YEAR(E3TimeStamp),4) AS Data,
Maquina AS Maquina,
Parametro AS Código,
ROUND(sum(Metrica),2) AS 'Metrica Total' ,
ROUND(SUM(CASE WHEN Turno = 1 THEN Metrica ELSE 0 END),2) As 'Metrica Turno 01',
ROUND(SUM(CASE WHEN Turno = 2 THEN Metrica ELSE 0 END),2) As 'Metrica Turno 02',
ROUND(SUM(CASE WHEN Turno = 3 THEN Metrica ELSE 0 END),2) As 'Metrica Turno 03'
FROM Tab_Dados_Escolha
GROUP BY 
YEAR(E3TimeStamp), MONTH(E3TimeStamp),DAY(E3TimeStamp), Maquina, Parametro

Result obtained from the above script:

Result that should occur (Shift 03 was added to the day before, as it has not started today):

Answer 1

The problem is that shift 3 ends the next day (starts at 22hrs and ends 6:00 the next day)

To solve this problem, consider that the date datetime must be manipulated in a way that "seems" to belong to the end of the previous day. After this manipulation, you can execute your query that will get the expected result. I made an example in Fiddle that is available in this link SQL Fiddle.

MS SQL Server 2017 Schema Setup:

CREATE TABLE tab_dados_escolha (
    Maquina varchar(100) COLLATE Latin1_General_CI_AS DEFAULT NULL NULL,
    Metrica decimal(10,0) DEFAULT NULL NULL,
    E3TimeStamp datetime DEFAULT NULL NULL,
    Turno int DEFAULT NULL NULL,
    Parametro int NULL
);

INSERT INTO tab_dados_escolha (Maquina,Metrica,E3TimeStamp,Turno,Parametro) VALUES
     (N'ESC_RET',10,'2021-05-13 05:00:00.0',3,125),
     (N'ESC_RET',7,'2021-05-14 03:00:00.0',3,125),
     (N'ESC_RET',22,'2021-05-13 10:00:00.0',1,125),
     (N'ESC_RET',15,'2021-05-13 16:00:00.0',2,125),
     (N'ESC_RET',2,'2021-05-13 23:00:00.0',3,125),
     (N'ESC_RET',4,'2021-05-14 23:00:00.0',3,125);

Consultation 1:

select * from tab_dados_escolha

Upshot:

Machine	Metrica	E3timestamp	Shift	Parameter
ESC_RET	10	2021-05-13T05:00:00Z	3	125
ESC_RET	7	2021-05-14T03:00:00Z	3	125
ESC_RET	22	2021-05-13T10:00:00Z	1	125
ESC_RET	15	2021-05-13T16:00:00Z	2	125
ESC_RET	2	2021-05-13T23:00:00Z	3	125
ESC_RET	4	2021-05-14T23:00:00Z	3	125

The Solution

Query 2:

with tab_dados_escolha_corrigido as
(
    Select * from tab_dados_escolha
    except
    Select Maquina, Metrica, E3TimeStamp, Turno, Parametro
      from tab_dados_escolha
     where DatePart(hour,E3TimeStamp) < 6
    Union
    Select Maquina, Metrica, cast(cast(cast(E3TimeStamp-1 as date) as varchar) + ' 23:59:00' as datetime) as E3TimeStamp, Turno, Parametro
      from tab_dados_escolha
     where DatePart(hour,E3TimeStamp) < 6
)
SELECT 
  convert(varchar,E3TimeStamp,103) AS Data,
  Maquina AS Maquina,
  Parametro AS Código,
  ROUND(sum(Metrica),2) AS 'Metrica Total' ,
  ROUND(SUM(CASE WHEN Turno = 1 THEN Metrica ELSE 0 END),2) As 'Metrica Turno 01',
  ROUND(SUM(CASE WHEN Turno = 2 THEN Metrica ELSE 0 END),2) As 'Metrica Turno 02',
  ROUND(SUM(CASE WHEN Turno = 3 THEN Metrica ELSE 0 END),2) As 'Metrica Turno 03'
FROM tab_dados_escolha_corrigido
GROUP BY convert(varchar,E3TimeStamp,103), Maquina, Parametro

Upshot:

Date	Machine	Code	Metrica Total	Metrica Turno 01	Metrica Turno 02	Metrica Turno 03
12/05/2021	ESC_RET	125	10	0	0	10
13/05/2021	ESC_RET	125	46	22	15	9
14/05/2021	ESC_RET	125	4	0	0	4

The idea

The idea was using set logic as follows: Take all table records with exception except the time of which is less than 6 hours. The result, make a union with all records that were deleted with the except, but manipulated the date to become the day before cast(cast(cast(E3TimeStamp-1 as date) as varchar) + ' 23:59:00' as datetime) as E3TimeStamp, thus forcing the date to point to the day on which the metrical field should be summed.

Command With ... As

The command with tab_dados_escolha_corrigido as () allows creating what is called CTE (common table Expression) or common table expression which is a kind of temporary table defined in the execution scope of a single SELECT statement. In your case, you could create a view calling for tab_dados_escolha_corrigido.

Convert

Was used the convert(varchar,E3TimeStamp,103) to make the date result in the format you highlighted in your original query.

Answer 2

Come on, let’s go, the query below should bring what you are needing

select Dataturno,
       Maquina AS Maquina,
       Parametro AS Código,
       ROUND(sum(Metrica),2) AS 'Metrica Total' ,
       ROUND(SUM(CASE WHEN Turno = 1 THEN Metrica ELSE 0 END),2) As 'Metrica Turno 01',
       ROUND(SUM(CASE WHEN Turno = 2 THEN Metrica ELSE 0 END),2) As 'Metrica Turno 02',
       ROUND(SUM(CASE WHEN Turno = 3 THEN Metrica ELSE 0 END),2) As 'Metrica Turno 03'
 from (
 select cast( iif( turno = 3 and datepart(hour, E3TimeStamp) < 6, dateadd( -1, day, E3TimeStamp ), E3TimeStamp ) as date ) as Dataturno,
       Maquina AS Maquina,
       Parametro AS Código,
       Turno,
       Metrica
  from Tab_Dados_Escolha
) x
group by 
    Dataturno, Maquina, Parametro,

I’ll explain what I did. in line 12: if the shift = 3 and the time < 6, I consider the E3timestamp as the previous day. So you’ll count the whole shift, like the day before. I used a cast, to bring only the date, and not need to catch the day, month and year.

I can’t test it, but if there’s a mistake and I can’t fix it, let me know I’m trying to help you.