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 |
+------------+-------+-------------+-------------+---------+------------+
MySQL TIMEDIFF() Function The TIMEDIFF() function returns the difference between two time/datetime expressions. Note: time1 and time2 should be in the same format, and the calculation is time1 - time2.
So Cast((@Dt2 - @Dt1) as Float) gives you total days between two datetimes. Multiply by 24 to convert to hours.
To calculate the difference between two dates in different columns, we use the two columns createdDate and LastLogin of the registration table and apply the DATEDIFF function on these columns. To find the difference between the two dates in different columns, we need two dates from the different columns.
DATEDIFF() is a basic SQL Server function that can be used to do date math. Specifically, it gets the difference between 2 dates with the results returned in date units specified as years, months days, minutes, seconds as an int (integer) value.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With