SQL Server - Consulta tiempos por rango de horas fijas

Consulta tiempos por rango de horas fijas

Publicado por Frantxo (3 intervenciones) el 29/09/2020 13:25:50

Muy buenas,
Tengo una tabla donde se van generando las incidencias en tiempo real, y quisiera obtener por franjas de horas fijas la duración de cada uno de ellos.

Expongo el ejemplo de los datos de detalle y cómo me gustaría que se visualizara.
El detalle que se almacenaría en la tabla Detalle sería:
Inicio Fin Turno Incidencia Duración en minutos
21/09/2020 7:00 21/09/2020 7:02 M INC1 2
21/09/2020 7:02 21/09/2020 7:09 M INC2 7
21/09/2020 7:09 21/09/2020 7:13 M INC1 4
21/09/2020 7:13 21/09/2020 7:16 M INC2 3
21/09/2020 7:16 21/09/2020 7:19 M INC1 3
21/09/2020 7:19 21/09/2020 7:20 M INC3 1
21/09/2020 7:20 21/09/2020 7:35 M INC1 15
21/09/2020 7:35 21/09/2020 7:40 M INC5 5
21/09/2020 7:40 21/09/2020 8:56 M INC1 76
21/09/2020 8:56 21/09/2020 8:58 M INC6 2
21/09/2020 8:58 21/09/2020 9:28 M INC1 30
21/09/2020 9:28 21/09/2020 9:32 M INC8 4
21/09/2020 9:32 21/09/2020 10:02 M INC1 30
21/09/2020 10:02 21/09/2020 10:10 M INC3 8
21/09/2020 10:10 21/09/2020 11:01 M INC1 51
21/09/2020 11:01 21/09/2020 11:08 M INC4 7
21/09/2020 11:08 21/09/2020 11:13 M INC6 5
21/09/2020 11:13 21/09/2020 11:40 M INC7 27
21/09/2020 11:40 21/09/2020 14:53 M INC1 193
21/09/2020 14:53 21/09/2020 15:00 M INC2 7
21/09/2020 15:00 21/09/2020 22:30 T INC0 450
21/09/2020 22:30 22/09/2020 7:00 N INC0 510

El informe/consulta resultante sería así:
DESDE HASTA TURNO INCIDENCIA TOTAL
21/09/2020 7:00 21/09/2020 8:00 M INC1 24
21/09/2020 7:00 21/09/2020 8:00 M INC2 10
21/09/2020 7:00 21/09/2020 8:00 M INC3 1
21/09/2020 7:00 21/09/2020 8:00 M INC5 5
Total 40
21/09/2020 8:00 21/09/2020 9:00 M INC6 2
21/09/2020 8:00 21/09/2020 9:00 M INC1 56
Total 58
21/09/2020 9:00 21/09/2020 10:00 M INC1 56
21/09/2020 9:00 21/09/2020 10:00 M INC8 4
Total 60
21/09/2020 10:00 21/09/2020 11:00 M INC3 8
21/09/2020 10:00 21/09/2020 11:00 M INC1 50
Total 58
21/09/2020 11:00 21/09/2020 12:00 M INC4 7
21/09/2020 11:00 21/09/2020 12:00 M INC6 5
21/09/2020 11:00 21/09/2020 12:00 M INC7 27
Total 39
21/09/2020 12:00 21/09/2020 13:00 M INC1 60
Total 60
21/09/2020 13:00 21/09/2020 14:00 M INC1 60
Total 60
21/09/2020 14:00 21/09/2020 15:00 M INC1 53
21/09/2020 14:00 21/09/2020 15:00 M INC2 7
Total 60
Total Turno 435
21/09/2020 15:00 21/09/2020 16:00 T INC0 60
21/09/2020 16:00 21/09/2020 17:00 T INC0 60
21/09/2020 17:00 21/09/2020 18:00 T INC0 60
21/09/2020 18:00 21/09/2020 19:00 T INC0 60
21/09/2020 19:00 21/09/2020 20:00 T INC0 60
21/09/2020 20:00 21/09/2020 21:00 T INC0 60
21/09/2020 21:00 21/09/2020 22:00 T INC0 60
Total 420
Total Turno 420
21/09/2020 22:00 21/09/2020 23:00 N INC0 60
21/09/2020 23:00 22/09/2020 0:00 N INC0 60
22/09/2020 0:00 22/09/2020 1:00 N INC0 60
22/09/2020 1:00 22/09/2020 2:00 N INC0 60
22/09/2020 2:00 22/09/2020 3:00 N INC0 60
22/09/2020 3:00 22/09/2020 4:00 N INC0 60
22/09/2020 4:00 22/09/2020 5:00 N INC0 60
22/09/2020 5:00 22/09/2020 6:00 N INC0 60
22/09/2020 6:00 22/09/2020 7:00 N INC0 60
Total 540
Total Turno 960
Total General 1815

Sé que es muy complejo y que posiblemente no se pueda realizar mediante una consulta, pero si se pudiera sería ideal.

Valora esta pregunta

Me gusta: Está pregunta es útil y esta clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

Pos: 1

Val: 3.250

Consulta tiempos por rango de horas fijas

Publicado por Isaias (4558 intervenciones) el 29/09/2020 13:41:40

Muy interesante, ¿Cómo distingues los TURNOS?

Valora esta respuesta

Me gusta: Está respuesta es útil y esta clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

Consulta tiempos por rango de horas fijas

Publicado por Frantxo (3 intervenciones) el 29/09/2020 13:47:46

Viene dada en una tabla maestro en la cual tiene el criterio por día, franja horaria y turno. Algo así:
Fecha Turno Inicio Fin
2020-09-21 M 2020-09-21 07:00:00 2020-09-21 15:00:00
2020-09-21 T 2020-09-21 15:00:00 2020-09-21 22:30:00
2020-09-21 N 2020-09-21 22:30:00 2020-09-22 07:00:00

Conseguí algo realizando la siguiente consulta pero no me devuelve los tiempos como quisiera respetando los cortes según las horas completas. La totalización la podría realizar a posteriori, no sería reiquisito para la propia consulta principal.

SELECT	 wsh.imputationDate, wsh.shift, wsh.workplace,

				Convert(smalldatetime,convert(varchar, wsh.startDate,111)+ ' '+ convert(varchar, RIGHT('00' + Ltrim(Rtrim(DATEPART([hour], wsh.startdate))),2) + ':00:00')) as Ini,

				DATEADD([hour], CEILING(DATEPART([hour], wsh.startDate) + (DATEPART([minute], wsh.startdate) / 60.00)), DATEADD(DAY, DATEDIFF(DAY, 0, wsh.startDate), 0)) AS dt,

				wsh.stateType, ST.stateTypeName,

				Sum(DateDiff(mi, wsh.startDate,wsh.endDate)) as duration

		FROM Detalle WSH

		inner Join Turnos WS on

					WSh.company = ws.company and

					wsh.workplace = ws.workplace and

					wsh.imputationDate = ws.imputationDate

		and wsh.startDate >= Convert(smalldatetime,convert(varchar, wsh.startDate,111)+ ' '+ convert(varchar, RIGHT('00' + Ltrim(Rtrim(DATEPART([hour], wsh.startdate))),2) + ':00:00'))

		and wsh.endDate <= DATEADD([hour], CEILING(DATEPART([hour], wsh.startDate) + (DATEPART([minute], wsh.startdate) / 60.00)), DATEADD(DAY, DATEDIFF(DAY, 0, wsh.startDate), 0))

		where wsh.company = 'slp'

			and wsh.imputationDate = '20200921'

			and wsh.workplace = 'SLPAF1'

		Group by  wsh.imputationDate, wsh.shift, wsh.workplace,wsh.stateType, ST.stateTypeName,

		Convert(smalldatetime,convert(varchar, wsh.startDate,111)+ ' '+ convert(varchar, RIGHT('00' + Ltrim(Rtrim(DATEPART([hour], wsh.startdate))),2) + ':00:00')) ,

		DATEADD([hour], CEILING(DATEPART([hour], wsh.startDate) + (DATEPART([minute], wsh.startdate) / 60.00)), DATEADD(DAY, DATEDIFF(DAY, 0, wsh.startDate), 0))

		order by 6,7

Consigo que me "trocee" los intervalos por horas completas, pero si una incidencia está entre varias franjas, la acumula en la primera de ellas, reportando más de 1 hora que es lo máximo que debería mostrarse. Si un intervalo abarca más de 1 hora como es el caso del detalle de los turnos de Tarde y noche, no me hace el sumatorio ni me muestra los intervalos.

Valora esta respuesta

Comentar

Pos: 4

Val: 122

Consulta tiempos por rango de horas fijas

Publicado por Plutarco (48 intervenciones) el 08/10/2020 16:05:48

Muy interesante el problema, no creo que se pueda en una vista solamente ya que veo en tus datos que hay incidencias que abarcan más de 1 hora (varias horas incluso) y eso implica que deben contabilizarse en varias franjas de horas, yo crearía primero una tabla temporal del día y le metería tantos registros como horas tiene cada turno o definitivamente 24 registros para que sea uno por cada hora, luego voy barriendo mi tabla de incidencias cada registro tiene el inicio de la incidencia así que debe entrar en la contabilización del renglón que ya tengo especificado, como cada incidencia tiene su cantidad en minutos, pues el máximo de minutos que agregaría en cada registro de mi tabla sería 60, si se pasan de 60 entonces obviamente busco la siguiente hora y le voy agregando así la diferencia en cada registro de mi tabla.

Espero haber sido claro

Saludos

Valora esta respuesta

Comentar

Consulta tiempos por rango de horas fijas

Publicado por Frantxo (3 intervenciones) el 09/10/2020 09:11:24

Gracias por la respuesta. Entiendo que es algo complejo de hacerlo en una única SQL. Lo que me propones era algo que tenía más o menos en mente, pero al ser algo que se utilizaría para un informe, intento evitar tener que generar tablas "intermedias" en la medida de lo posible. Tendré que investigar algún tipo de "algoritmo" que cuando la duración supere los 60 minutos, la diferencia sea asignada a la siguiente hora y así sucesivamente.

Gracias de nuevo.

Valora esta respuesta

Comentar

Sevidor Vinculado por ODBC

Servidor vinculado