Although it seems a simple case, there is an additional complication that is the fact that the day of the launch should be set to the day before when the turn crosses day and the point was hit the day after the start of the turn. This is case 744 in the example posted by the author, on what date/time of the strike:
2016-06-25 00:05:17.852
is changed to the previous day:
2016-06-24
As the shifts start at full hour (always at 0 minute), one simple way is to transform the turn table per hour:
on a table of shifts per hour, starting at 0h and going up to 23h:
One way to get the hourly shift table is the one in the code below.
-- código #1
declare @tbHora table (Hora int);
INSERT into @tbHora values
(0), (1), (2), (3), (4), (5), (6), (7),
(8), (9), (10), (11), (12), (13), (14), (15),
(16), (17), (18), (19), (20), (21), (22), (23);
-- transforma a tabela de turno de faixa horária para hora
with
cteTurnoFH as (
SELECT codigo, inicio, dateadd(minute, duracao, inicio) as fim
from tbTurno
),
cteTurnoH as (
SELECT T.codigo, H.Hora
from cteTurnoFH as T
cross join @tbHora as H
where 1 = case when datepart(day, T.inicio) = datepart(day, T.fim)
then case when H.Hora >= datepart(hour, T.inicio)
and H.Hora < datepart(hour, T.fim)
then 1 else 0 end
else case when H.Hora >= datepart(hour, T.inicio)
or H.Hora < datepart(hour, T.fim)
then 1 else 0 end
end
)
...
And to show off the launches with the turn would suffice something like
-- código #2
...
SELECT L.serie, cast(L.hora as date) as dia, TH.codigo
from tbLançamento as L
inner join cteTurnoH as TH on TH.Hora = datepart(hour, L.hora)
order by L.serie;
However, there is an additional complication, which is the fact that the day of release must be set to the day before when the turn crosses the day and the spike was hit the day after the start of the turn. This is case 744, in the example posted by the author.
To treat the day setting, the CTE cteTurnoH has been enlarged, containing column indicating at which times it is necessary to adjust the date. At the end the code went like this:
-- código #3 v2
set nocount on;
--
IF Object_ID('tempDB..#TurnoH', 'U') is not null
DROP TABLE #TurnoH;
CREATE TABLE #TurnoH (Hora int primary key, codigo char(3), AjusteDia int);
--
declare @tbHora table (Hora int);
INSERT into @tbHora values
(0), (1), (2), (3), (4), (5), (6), (7),
(8), (9), (10), (11), (12), (13), (14), (15),
(16), (17), (18), (19), (20), (21), (22), (23);
-- transforma a tabela de turno de faixa horária para hora
with
cteTurnoFH as (
SELECT codigo, inicio, dateadd(minute, duracao, inicio) as fim
from tbTurno
),
cteTurnoH as (
SELECT H.Hora, T.codigo,
case when datepart(day, T.inicio) = datepart(day, T.fim)
then 0
else case when datepart(hour, T.inicio) <= H.Hora
then 0
else -1
end
end as AjusteDia
from cteTurnoFH as T
cross join @tbHora as H
where 1 = case when datepart(day, T.inicio) = datepart(day, T.fim)
then case when H.Hora >= datepart(hour, T.inicio)
and H.Hora < datepart(hour, T.fim)
then 1 else 0 end
else case when H.Hora >= datepart(hour, T.inicio)
or H.Hora < datepart(hour, T.fim)
then 1 else 0 end
end
)
INSERT into #TurnoH (Hora, codigo, AjusteDia)
SELECT Hora, codigo, AjusteDia
from cteTurnoH;
--
SELECT L.serie, L.hora as batida,
cast(dateadd(day, TH.AjusteDia, L.hora) as date) as dia,
TH.codigo
from tbLançamento as L
inner join #TurnoH as TH on TH.Hora = datepart(hour, L.hora);
And the final result obtained is
If the turn is possible to start at fractional time, code #3 needs to be changed.
There’s probably a more efficient solution, but it was the first that came to mind this morning.
It seems to me that a simple Join + Between solves. Let’s see if someone posts an example.
– Bacco
@Antoniomano: Is the example of exit to line 744 correct? That is, although the point beat occurred on the day 2016-06-25, the result should include the start date of the turn (2016-06-24) ? // The solution depends on the answer.
– José Diz
Was any of the answer helpful? Don’t forget to choose one and mark it so it can be used if someone has a similar question!
– Sorack