获取SQL Server 2012两次之间的差异

The background:

背景:

I am trying to identify the length of time a Customer spends in a particular room. Each Customer is identifiable by a CustomerID, and when they visit they are assigned a VisitNumber. For example, if the customer visited today they would get a VisitNumber of say 111111. They would then leave and come back next week and would have a VisitNumber of 111112.

我试着确定客户在某一特定房间的时间长度。每个客户都可以被CustomerID识别，当他们访问时，他们被分配一个VisitNumber。例如，如果客户今天访问，他们会得到一个VisitNumber，例如111111。然后他们将离开，并在下周回来，并将有一个111112的参观人数。

When a customer first visits they are not initially assigned a room, and when they are finally assigned their designated room an entry is written to the database. CurrentRoom would be blank as they don't have a room yet, and the NewRoom is the room which they have been moved into.

当客户第一次访问时，他们最初不会被分配一个房间，当他们最终被分配一个指定的房间时，一个条目将被写入数据库。现在的房间是空的，因为他们还没有房间，新的房间就是他们搬进去的那个房间。

This entry would be recorded as event 1 (Customer moved from no room to a room), and the time is when the transaction takes place. If the customer is then moved in the future during their existing stay that would be recorded as event 9 (Customer moved from a room to another room), and the CurrentRoom & *NewRoom values would also be recorded.

此条目将被记录为事件1(客户从无房间移动到房间)，时间是发生事务的时间。如果客户在他们的现有住宿期间被移动，将被记录为事件9(客户从一个房间移动到另一个房间)，那么CurrentRoom & *NewRoom值也将被记录下来。

The problem

这个问题

I have managed to get the time from the previous row and the time from the next row using LAG and LEAD and then work out the difference between the two times which gives me the length of time the customer spent in that particular room.

我已经设法从前一行得到时间，从下一行得到时间，使用了滞后和超前，然后计算出这两个时间之间的差异，这给了我客户在那个特定房间里所花费的时间。

The issue when using LAG is it is obtaining the previous value, which in some cases could be the value from a completely different customer. I would like to get the LAG & LEAD values only for a particular CustomerID & the current VisitNumber and then work out the difference between the values to find out how long that customer spent in a room.

使用滞后的问题是它获得了以前的值，在某些情况下可能是来自完全不同的客户的值。我想要得到只有特定的客户id和当前的VisitNumber的滞后和领先值，然后计算出这些值之间的差异，以确定客户在一个房间里呆了多长时间。

Demo data:

演示数据:

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

This is the query that I have so far; this will give me the previous row value and next row value, but I don't think it takes the customer into account.

这是到目前为止的查询;这将给出前一行值和下一行值，但我不认为它考虑了客户。

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

Expected output: This is the output I am expecting. I only want the TimeInRoom (excluding the date field from the time):

期望输出:这是我期望的输出。我只需要TimeInRoom(不包括从time开始的date字段):

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

3 个解决方案

#1

I hope this helps:

我希望这有助于:

;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

May be I didn't understand very well your question, but try to use the clause PARTITION BY inside your LAG / LEAD functions:

也许我不是很理解你的问题，但是试着在你的滞后/引导函数中使用子句分区:

,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

In your example you will have a problem when a customer would visit one time, and using lag/lead you will get another client's visiting information.

在您的示例中，当一个客户要访问一次时，您将遇到一个问题，使用lag/lead您将获得另一个客户的访问信息。

Try it:

试一试:

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

#1