Получите разницу между двумя версиями для SQL Server 2012

Фон:

Я пытаюсь определить время, которое Клиент проводит в определенной комнате. Каждый клиент идентифицируется с помощью CustomerID, а при их посещении им присваивается VisitNumber. Например, если клиент посетил сегодня, он получит VisitNumber, например, 111111. Затем они уйдут и вернутся на следующей неделе и будут иметь VisitNumber из 111112.

Когда клиент сначала посещает, им первоначально не назначается комната, а когда они, наконец, назначаются назначенной комнате, запись записывается в базу данных. CurrentRoom будет пустым, поскольку у них еще нет комнаты, а NewRoom - это комната, в которую они были перемещены.

Эта запись будет записана как событие 1 (Клиент перемещен из комнаты в комнату), и время, когда происходит транзакция. Если клиент будет перемещен в будущем во время своего пребывания, которое будет записано как событие 9 (Клиент перемещен из комнаты в другую комнату), а также будут записаны значения CurrentRoom и * NewRoom.

Проблема

Мне удалось получить время из предыдущей строки и время из следующей строки с помощью LAG и LEAD, а затем разделить разницу между двумя дает мне время, проведенное клиентом в этой конкретной комнате.

Проблема при использовании LAG заключается в получении предыдущего значения, которое в некоторых случаях может быть значением от совершенно другого клиента. Я хотел бы получить значения LAG и LEAD только для определенного CustomerID и текущего VisitNumber, а затем определить разницу между значениями, чтобы узнать, как долго, что клиент провел в комнате.

Демо-данные:

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

Это запрос, который у меня есть до сих пор; это даст мне значение предыдущей строки и значение следующей строки, но я не думаю, что он учитывает клиента.

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

Ожидаемый результат: Это результат, который я ожидаю. Мне нужно только TimeInRoom (исключая поле даты из времени):

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

Ответ 1

Надеюсь, это поможет:

;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

Ответ 2

Возможно, я не очень хорошо понял ваш вопрос, но попытаюсь использовать предложение PARTITION BY внутри ваших функций 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

Ответ 3

В вашем примере у вас будет проблема, когда клиент посетит один раз, и используя lag/lead, вы получите другую информацию о посещении клиента.

Попробуйте:

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