CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
richinsql
richinsql

Reputation: 332

Get difference between two times for 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.

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

Answers (3)

Gouri Shankar Aechoor
Gouri Shankar Aechoor

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

etsa
etsa

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

Vecchiasignora
Vecchiasignora

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

Related Questions