MS SQL Server time between records

Sample Data

declare @ewMessages table
(
MessageID BIGINT
, SalesSiteID INT
, IndividualID BIGINT
, DirectionID INT
, DateAdded DATETIME

)


insert into @ewMessages (messageID, SalesSiteID, IndividualID, DirectionID, DateAdded)
values 
(172549, 91727, 2019721, 2, '2017-02-25 15:08:56.083')
,(172548, 91727, 2019721, 1, '2017-02-25 15:07:54.780')
,(172547, 91727, 2019721, 2, '2017-02-25 15:06:51.020')
,(172514, 91727, 2019721, 1, '2017-02-25 13:42:55.973')
,(172553, 91727, 2019722, 1, '2017-02-25 15:08:56.083')
,(172551, 91727, 2019722, 2, '2017-02-25 15:06:51.020')
,(172590, 91727, 2019722, 1, '2017-02-25 15:08:55.973')

Select allOpen in new window

Query:

DECLARE @IndividualID BIGINT
SET @IndividualID = 2019721

; WITH data
AS
(
	-- Sort data in DESC order and assign row number
	SELECT  MessageID ,
			SalesSiteID ,
			IndividualID,
			DirectionID ,
			DateAdded,
			ROW_NUMBER() OVER(
				PARTITION BY IndividualID, SalesSiteID
				ORDER BY IndividualID, SalesSiteID, MessageID DESC 
			) AS MessageOrder
	FROM    @ewMessages
	WHERE   DateAdded > GETDATE() - 1
	AND		IndividualID = @IndividualID
)			
SELECT  d1.MessageID AS CurrentMessageID
		, d2.MessageID AS PrevMessageID
		, dateDiff(s, d2.DateAdded, d1.DateAdded) SecondsSincePrevMessage
		, d1.*
FROM	data d1 
			LEFT JOIN data d2 ON d1.MessageOrder = d2.MessageOrder-1
ORDER BY d1.MessageOrder;

Select allOpen in new window

Agx
I am traveling at the moment but will take a look this evening EST

The answer may depend on which version on MS SQL Server you use.


LEAD() and LAG() are the relevant functions for this request (starting with 2012), but you may not have them available to you. e.g.

DECLARE @IndividualID bigint
SET @IndividualID = 2019721
SELECT
      MessageID
    , SalesSiteID
    , IndividualID
    , DirectionID
    , DateAdded
    , LAG(DateAdded) OVER(PARTITION BY SalesSiteID,IndividualID 
                          ORDER BY DateAdded desc) AS PrevDateAdded

    , DATEDIFF(second,LAG(DateAdded) OVER(PARTITION BY SalesSiteID,IndividualID 
                          ORDER BY DateAdded desc), DateAdded) AS ResponseTime
FROM dbo.ewMessages
WHERE DateAdded > GETDATE() - 1
AND IndividualID = @IndividualID
ORDER BY
      IndividualID
    , SalesSiteID
    , MessageID DESC
;

Select allOpen in new window

The following alternative may work for you if LAG isn't available. This uses OUTER APPLY and a TOP 1 subquery:

DECLARE @IndividualID bigint
SET @IndividualID = 2019721
SELECT
      MessageID
    , SalesSiteID
    , IndividualID
    , DirectionID
    , DateAdded
    , DATEDIFF(SECOND,PrevDateAdded, DateAdded) AS ResponseTime
FROM dbo.ewMessages AS d
OUTER APPLY (
      SELECT TOP 1
            DateAdded AS PrevDateAdded
      FROM dbo.ewMessages AS p
      WHERE d.SalesSiteID = p.SalesSiteID
      AND d.IndividualID = p.IndividualID
      ORDER BY DateAdded DESC
) AS OA
WHERE DateAdded > GETDATE() - 1
AND IndividualID = @IndividualID
ORDER BY
      IndividualID
    , SalesSiteID
    , MessageID DESC
;

Select allOpen in new window

NULL cannot be compared using equals, or not equals

so this predicate:

                  AND IndividualID = @IndividualID

will never return any rows where IndividualID  is null

so that makes this predicate:

                  AND ISNULL(IndividualID, 0) > 0

redundant. Plus, using ISNULL() this way can be a clause of slowness in queries.
Be wary of using functions in the where clause, avoid them if possible.

---- & ----
In future questions would you add some re-usable sample data instead of an image?
e.g. copy/paste the data from that result into a code block within the question

[+edit]just like _agx_ did in that answer - which I had not seen while preparing mine :)

Sure thing
I will be at a desk later this evening

@PortletPaul - Cool. Didn't know about LEAD/LAG in 2012.  Learn something new .... :-)

Spectacular.
Implemented and working flawlessly

Out of curiosity, did you ever try the query I suggested?  

Edit: Wondering how it stacked up against Paul's ;-)  If it didn't work, it's good to know why in case it's something to avoid in future.

@_agx_

I would expect the row_number() based approach to work too.

Hard to gauge the performance implications without testing but the apply operator is often surprisingly efficient even if it does involve a correlated subquery (anecdotally - the optimiser appears to handle this particularly well).

Had LEAD/LAG been available I would expect that approach to be the best for performance.

>> the apply operator is often surprisingly efficient even if it does involve a correlated subquery

@PortletPaul - Funny you should say that. I've always liked the "cleaner" apply syntax, but worried about the correlated subquery aspect.  I'll have to look into that more and get a better idea of what it's doing beneath the hood. Thanks!

Hey guys
I ran them all against a very large table of data and the one I chose came out slightly quicker.

That was the only reason I chose it

And oh yeah.... the lag and lead was fun to see as well

Thanks for the feedback - always useful -  and appreciated!

Ditto.  Thanks guys!