How to calculate the date and code of the shift?

See if that's what you need.

declare @horas table
(
  serie int,
  hora datetime
)


insert into @horas values
(743,'2016-06-24 23:06:19.663'),
(744,'2016-06-25 00:05:17.852'),
(745,'2016-06-25 08:01:27.001'),
(746,'2016-06-25 16:08:01.923')


select  serie, Convert(varchar(10),hora,126),
case 
    when DATEPART(HOUR, hora) BETWEEN 5 and 14  then 'S01'
    when DATEPART(HOUR, hora) BETWEEN 14 and 22 then 'S02' 
    when DATEPART(HOUR, hora) >= 23 or DATEPART(HOUR, hora) BETWEEN 0 and 5  then 'S03' ) then 'S03'
end as codigo

from @horas

Although it sounds like a simple case, there is an additional complication that is that the day of the launch must be set to the previous day when the day crosses the day and the point was struck the day after the start of the turn. This is case 744 in the example posted by the author, at which beat date / time:

2016-06-25 00:05:17.852

changes to the previous day:

2016-06-24

Since shifts begin at full time (always at 0 minutes), a simple way is to turn the shift table by time slot:

inatableofshiftsperhour,startingat0handgoinguntil11h:

Onewaytogetthehourlyshifttableisasinthefollowingcode.

--código#1declare@tbHoratable(Horaint);INSERTinto@tbHoravalues(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23);--transformaatabeladeturnodefaixahoráriaparahorawithcteTurnoFHas(SELECTcodigo,inicio,dateadd(minute,duracao,inicio)asfimfromtbTurno),cteTurnoHas(SELECTT.codigo,H.HorafromcteTurnoFHasTcrossjoin@tbHoraasHwhere1=casewhendatepart(day,T.inicio)=datepart(day,T.fim)thencasewhenH.Hora>=datepart(hour,T.inicio)andH.Hora<datepart(hour,T.fim)then1else0endelsecasewhenH.Hora>=datepart(hour,T.inicio)orH.Hora<datepart(hour,T.fim)then1else0endend)...

Andtodisplaythepostingswiththeshiftwouldbesomethinglike

--código#2...SELECTL.serie,cast(L.horaasdate)asdia,TH.codigofromtbLançamentoasLinnerjoincteTurnoHasTHonTH.Hora=datepart(hour,L.hora)orderbyL.serie;

However,thereisanadditionalcomplication,whichisthatthelaunchdaymustbesettothepreviousdaywhentheshiftcrossesthedayandthepointwasstruckthedayafterthestartoftheturn.Thisiscase744,intheexamplepostedbytheauthor.

Tohandlethedaysetting,theCTEcteTurnoHhasbeenexpanded,containingacolumnindicatingwhichtimesthedateneedstobeadjusted.Attheendthecodelookslikethis:

--código#3v2setnocounton;--IFObject_ID('tempDB..#TurnoH','U')isnotnullDROPTABLE#TurnoH;CREATETABLE#TurnoH(Horaintprimarykey,codigochar(3),AjusteDiaint);--declare@tbHoratable(Horaint);INSERTinto@tbHoravalues(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23);--transformaatabeladeturnodefaixahoráriaparahorawithcteTurnoFHas(SELECTcodigo,inicio,dateadd(minute,duracao,inicio)asfimfromtbTurno),cteTurnoHas(SELECTH.Hora,T.codigo,casewhendatepart(day,T.inicio)=datepart(day,T.fim)then0elsecasewhendatepart(hour,T.inicio)<=H.Horathen0else-1endendasAjusteDiafromcteTurnoFHasTcrossjoin@tbHoraasHwhere1=casewhendatepart(day,T.inicio)=datepart(day,T.fim)thencasewhenH.Hora>=datepart(hour,T.inicio)andH.Hora<datepart(hour,T.fim)then1else0endelsecasewhenH.Hora>=datepart(hour,T.inicio)orH.Hora<datepart(hour,T.fim)then1else0endend)INSERTinto#TurnoH(Hora,codigo,AjusteDia)SELECTHora,codigo,AjusteDiafromcteTurnoH;--SELECTL.serie,L.horaasbatida,cast(dateadd(day,TH.AjusteDia,L.hora)asdate)asdia,TH.codigofromtbLançamentoasLinnerjoin#TurnoHasTHonTH.Hora=datepart(hour,L.hora);

Andthefinalresultis

If there is the possibility of a shift starting in fractional time, code # 3 needs to be changed.

There is probably a more efficient solution, but it was the first one that came to mind this morning.

First, the best data type for the start of the shift is TIME , after all you do not need the DATE part of the data. Since the shift can start in one day and end in the other, the BETWEEN condition would not work with just one record. You can do the transformation of the records to facilitate validation with WITH :

WITH horas_ajustadas AS
(
  SELECT h.codigo,
         h.descricao,
         CAST(h.inicio AS TIME) AS inicio,
         DATEADD(MINUTE, h.duracao, CAST(h.inicio AS TIME)) AS fim
    FROM horas h
),
turnos AS
(
  -- Turnos que iniciam e terminam no mesmo dia
  SELECT ha.codigo,
         ha.descricao,
         ha.inicio,
         ha.fim,
         0 AS diferenca
    FROM horas_ajustadas ha
   WHERE ha.inicio < ha.fim
  -- Turnos que terminam no dia seguinte
  UNION ALL
  SELECT ha.codigo,
         ha.descricao,
         CAST('00:00:00.000' AS TIME) AS inicio,
         ha.fim,
         -1 AS diferenca
    FROM horas_ajustadas ha
   WHERE ha.inicio > ha.fim
  UNION ALL
  SELECT ha.codigo,
         ha.descricao,
         ha.inicio,
         CAST('23:59:59.999' AS TIME) AS fim,
         0 AS diferenca
    FROM horas_ajustadas ha
   WHERE ha.inicio > ha.fim
)
SELECT t.codigo,
       t.descricao,
       t.inicio,
       t.fim,
       t.diferenca
  FROM turnos t

Then cross the data with INNER JOIN into the created table:

...
SELECT d.serie,
       t.codigo,
       t.descricao,
       d.hora
  FROM dados d
       INNER JOIN turnos t ON CAST(d.hora AS TIME) BETWEEN t.inicio AND t.fim

Combining the two parts:

WITH horas_ajustadas AS
(
  SELECT h.codigo,
         h.descricao,
         CAST(h.inicio AS TIME) AS inicio,
         DATEADD(MINUTE, h.duracao, CAST(h.inicio AS TIME)) AS fim
    FROM horas h
),
turnos AS
(
  -- Turnos que iniciam e terminam no mesmo dia
  SELECT ha.codigo,
         ha.descricao,
         ha.inicio,
         ha.fim,
         0 AS diferenca
    FROM horas_ajustadas ha
   WHERE ha.inicio < ha.fim
  -- Turnos que terminam no dia seguinte
  UNION ALL
  SELECT ha.codigo,
         ha.descricao,
         CAST('00:00:00.000' AS TIME) AS inicio,
         ha.fim,
         -1 AS diferenca
    FROM horas_ajustadas ha
   WHERE ha.inicio > ha.fim
  UNION ALL
  SELECT ha.codigo,
         ha.descricao,
         ha.inicio,
         CAST('23:59:59.999' AS TIME) AS fim,
         0 AS diferenca
    FROM horas_ajustadas ha
   WHERE ha.inicio > ha.fim
)
SELECT d.serie,
       t.codigo,
       t.descricao,
       CONVERT(VARCHAR(10), DATEADD(DAY, t.diferenca, d.hora), 103) AS DATA
  FROM dados d
       INNER JOIN turnos t ON CAST(d.hora AS TIME) BETWEEN t.inicio AND t.fim
 ORDER BY d.serie

The result of the previous query is as follows:

serie | dia         | codigo
------|-------------|-------
743   | 24/06/2016  | S03
744   | 24/06/2016  | S03
745   | 25/06/2016  | S01
746   | 25/06/2016  | S02

See working in SQL Fiddle >.

You can find the turn of each release by making a direct relationship between the lançamentos and turnos tables.

It may seem like a more complex solution, but it leaves the query generic, that is, if the shift table changes, you do not need to change the query.

Conceptually, the relationship logic between the tables would be as follows:

FROM lançamento
LEFT JOIN turno
     ON (lançamento.hora em D+0) between (turno.inicio em D+0) AND (turno.término em D+0)
     or (lançamento.hora em D+1) between (turno.inicio em D+0) AND (turno.término em D+1)

D+0 corresponds to the default day (in the case of SQLServer, the default is 01-01-1900) and D+1 corresponds to the next day.

The idea is to relate records whose hora de lançamento em D+0 is between início and término of the turn.

Since one shift can start in one day and end the next day, you also need to check that hora do lançamento em D+1 is between início and término of shift.

Solution

The solution below is available for online testing at link

SELECT a.serie
      ,CAST(a.hora as DATE) as dia
      ,b.codigo
  FROM Lancamentos a
  LEFT JOIN Turnos b 
       ON CAST( CAST(a.hora as TIME) as datetime) -- hora em D+0
          BETWEEN inicio AND DATEADD( minute, duracao, inicio ) -- entre inicio AND término do turno
          OR DATEADD( day, 1, CAST( CAST( a.hora as TIME ) as datetime) ) -- hora em D+1
             BETWEEN inicio AND DATEADD( minute, duracao, inicio ) -- entre inicio AND término (no caso de término do turno em D+1)

I used the solution suggested by @fernando as subquery (MySql), I found it more interesting because in the case of errors in conditions the query returns error, making evaluation easier.

In my case, I also had a working shift.

See: