type - sql server 2017

Consigue la diferencia entre dos veces para SQL Server 2012

Question

El fondo:

Estoy tratando de identificar la cantidad de tiempo que un cliente pasa en una habitación en particular. Cada Cliente es identificable por un CustomerID , y cuando lo visitan se les asigna un VisitNumber . Por ejemplo, si el cliente lo visitara hoy, obtendrían un VisitNumber de 111111. Se irían y volverían la próxima semana y tendrían un VisitNumber de 111112.

Cuando un cliente visita por primera vez, inicialmente no se les asigna una habitación, y cuando finalmente se les asigna su habitación designada, se escribe una entrada en la base de datos. CurrentRoom estaría en blanco ya que aún no tienen una habitación, y NewRoom es la habitación a la que se han trasladado.

Esta entrada se registraría como evento 1 (El cliente se mudó de una habitación a otra) y la hora es cuando se realiza la transacción. Si el cliente se traslada en el futuro durante su estadía existente , se registrará como evento 9 (El cliente se trasladó de una habitación a otra habitación) y los valores de CurrentRoom y * NewRoom también se registrarán.

El problema

Me las arreglé para obtener el tiempo de la fila anterior y el tiempo de la siguiente fila con LAG y LEAD y luego calcular la diferencia entre las dos veces, lo que me da el tiempo que el cliente pasó en esa habitación en particular.

El problema al utilizar LAG es que está obteniendo el valor anterior, que en algunos casos podría ser el valor de un cliente completamente diferente. Me gustaría obtener los valores de LAG y LEAD solo para un CustomerID particular y el VisitNumber actual y luego calcular la diferencia entre los valores para averiguar cuánto tiempo pasó el cliente en una habitación.

Datos de demostración:

CREATE TABLE #beds
(
[id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[User] [nvarchar](50) NULL,
[CustomerID] [nvarchar](50) NULL,
[Area] [nchar](10) NULL,
[Event] [nvarchar](50) NULL,
[VisitNumber] [nvarchar](50) NULL,
[Time] [datetime] NULL,
[CurrentRoom] [nvarchar](50) NULL,
[NewRoom] [nvarchar](50) NULL
)
GO
INSERT INTO #beds ([User],[CustomerID],[Area],[Event],[VisitNumber],[Time],[CurrentRoom],[NewRoom])
VALUES (''00001'',''C11111111'',''Area1'',2,111111111,''2017-03-22 11:05:44.360'',''B22'',''B44''),
(''00001'',''C11111111'',''Area1'',1,111111111,''2017-03-22 11:05:15.517'','''',''B22''),
(''00001'',''C22222222'',''Area2'',1,222222222,''2017-03-22 07:38:16.117'','''',''POD3''),
(''00001'',''C22222222'',''Area2'',3,222222222,''2017-03-22 07:41:24.787'',''POD3'',''POD3''),
(''00001'',''C22222222'',''Area2'',9,222222222,''2017-03-22 09:10:49.697'',''POD3'',''''),
(''00001'',''C22222222'',''Area2'',1,222222222,''2017-03-22 10:05:19.130'','''',''POD15''),
(''00001'',''C22222222'',''Area2'',2,222222222,''2017-03-22 10:13:43.057'',''POD15'',''A''),
(''00001'',''C22222222'',''Area2'',3,222222222,''2017-03-22 10:25:01.527'',''A'',''A''),
(''00001'',''C22222222'',''Area2'',3,222222222,''2017-03-22 10:46:03.960'',''A'',''A''),
(''00001'',''C22222222'',''Area2'',3,222222222,''2017-03-22 10:46:17.030'',''A'',''A''),
(''00002'',''C33333333'',''Area3'',1,333333333,''2017-03-22 09:20:23.660'','''',''B46''),
(''00001'',''C33333333'',''Area2'',9,333333333,''2017-03-22 08:53:32.860'',''POD8'',''POD1''),
(''00001'',''C33333333'',''Area2'',1,333333333,''2017-03-22 07:34:58.810'',''POD7'',''POD8''),
(''00001'',''C33333333'',''Area2'',1,333333333,''2017-03-22 11:49:55.203'','''',''BB4''),
(''00001'',''C33333333'',''Area2'',3,333333333,''2017-03-22 11:50:11.943'',''BB4'',''BB4''),
(''00001'',''C33333333'',''Area2'',3,333333333,''2017-03-22 08:42:56.157'',''POD8'',''POD8''),
(''00001'',''C33333333'',''Area2'',3,333333333,''2017-03-22 08:22:59.157'',''POD8'',''POD8''),
(''00003'',''C33333333'',''Area3'',1,333333333,''2017-03-23 06:41:12.753'','''',''B46'')
GO

Esta es la consulta que tengo hasta ahora; esto me dará el valor de la fila anterior y el valor de la fila siguiente, pero no creo que tenga en cuenta al cliente.

SELECT
T1.[User], T1.[CustomerID],
T1.[Area], T1.[Event],
T1.[VisitNumber],
T1.[CurrentRoom], T1.[NewRoom],
T1.[Time],
LAG(T1.TIME) OVER (ORDER BY T1.VisitNumber) PreviousTime,
LEAD(T1.TIME) OVER (ORDER BY T1.VisitNumber) NextTime
FROM
#beds t1
WHERE
T1.[Area] = ''Area2''
AND T1.[CurrentRoom] IS NOT NULL
AND T1.[NewRoom] IS NOT NULL
AND T1.[CustomerID] IS NOT NULL
AND T1.[CustomerID] <> '' ''
AND T1.Event IN (1,9)
ORDER BY
VisitNumber DESC

Salida esperada: esta es la salida que estoy esperando. Solo quiero el TimeInRoom (excluyendo el campo de fecha de la hora):

+------------+-------+-------------+-------------+---------+------------+
| CustomerID | Area | VisitNumber | CurrentRoom | NewRoom | TimeInRoom |
+------------+-------+-------------+-------------+---------+------------+
|C33333333 |Area2 | 333333333 | | BB4 | 00:10 |
|C33333333 |Area2 | 333333333 | | POD8 | 00:20 |
|C33333333 |Area2 | 333333333 | POD8 | | 00:30 |
+------------+-------+-------------+-------------+---------+------------+

Answer 1

En su ejemplo, tendrá un problema cuando un cliente lo visitaría una vez, y al usar el programa lag / lead obtendrá la información de visita de otro cliente.

Intentalo:

SELECT
T1.[User], T1.[CustomerID],
T1.[Area], T1.[Event],
T1.[VisitNumber],
T1.[CurrentRoom], T1.[NewRoom],
T1.[Time],
(select TOP (1) t.Time from #beds t where t.[CustomerID] = T1.[CustomerID] and t.Time<T1.Time order by t.Time desc) PreviousTime,
(select TOP (1) t.Time from #beds t where t.[CustomerID] = T1.[CustomerID] and t.Time>T1.Time order by t.Time) NextTime
FROM
#beds t1
WHERE
T1.[Area] = ''Area2''
AND T1.[CurrentRoom] IS NOT NULL
AND T1.[NewRoom] IS NOT NULL
AND T1.[CustomerID] IS NOT NULL
AND T1.[CustomerID] <> '' ''
AND T1.Event IN (1,9)
ORDER BY
VisitNumber DESC

Answer 2

Espero que esto ayude:

;WITH cte_Result AS
(
SELECT
[CustomerID],
[Area],
[VisitNumber],
[CurrentRoom],
[NewRoom],
[Time],
LAG([TIME]) OVER (partition by [CustomerID],[VisitNumber] ORDER BY ID DESC) PreviousTime,
LEAD([TIME]) OVER (partition by [CustomerID],[VisitNumber] ORDER BY ID DESC) NextTime
FROM #beds
WHERE [Area] = ''Area2''
AND [CurrentRoom] IS NOT NULL
AND [NewRoom] IS NOT NULL
AND [CustomerID] IS NOT NULL
AND [CustomerID] <> '' ''
AND [Event] IN (1,9)
--AND [CustomerID] = ''C33333333''
),
cte_BuildStayPeriod
AS (
SELECT CustomerID,
Area,
VisitNumber,
CurrentRoom,
NewRoom,
DATEDIFF(SECOND, COALESCE([NextTime], PreviousTime), COALESCE(PreviousTime, [time])) AS StayDuration
FROM cte_Result
)
SELECT CustomerID,
Area,
VisitNumber,
CurrentRoom,
NewRoom,
StayDuration,
CAST(DATEADD(SECOND, StayDuration, ''1900-01-01'') AS TIME) AS StayDuration
FROM cte_BuildStayPeriod

Answer 3

Puede que no haya entendido muy bien su pregunta, pero intente usar la cláusula PARTITION BY dentro de sus funciones LAG / LEAD:

,LAG(T1.TIME) OVER (PARTITION BY CustomerID ORDER BY T1.VisitNumber) PreviousTime
,LEAD(T1.TIME) OVER (PARTITION BY CustomerID ORDER BY T1.VisitNumber) NextTime