Reputation: 332
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.
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.
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.
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):
+------------+-------+-------------+-------------+---------+------------+
| CustomerID | Area | VisitNumber | CurrentRoom | NewRoom | TimeInRoom |
+------------+-------+-------------+-------------+---------+------------+
|C33333333 |Area2 | 333333333 | | BB4 | 00:10 |
|C33333333 |Area2 | 333333333 | | POD8 | 00:20 |
|C33333333 |Area2 | 333333333 | POD8 | | 00:30 |
+------------+-------+-------------+-------------+---------+------------+
Upvotes: 17
Views: 431
Reputation: 1581
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
Upvotes: 4
Reputation: 5060
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
Upvotes: 4
Reputation: 1315
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.
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
Upvotes: 0