SQL Rolling DateTime Difference

Hi All,

I have a table in sql that holds a very basic event sequence.

ID : int
TimeStamp : DateTime
Value : Bit

Is it possible to query this table to show the time in minutes between each entry? The order would be providd by the ID column.

I would like to show each row but add a colum that shows the diference (in minutes) from the previous row.

this is possible?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yes it is. Here you go..

;with CTE AS
 (SELECT *,dense_RANK() OVER (PARTITION BY NULL ORDER BY id) AS rankValue FROM <Yourtable>)
select t1.ID,
           (DATEDIFF(ms,t1.TimeStamp ,t2.TimeStamp ))/60000.0 AS  DifferenceInMin
from CTE t1 with (Nolock)
left outer JOIN CTE  t2 with (Nolock)
ON  t1.rankValue = t2.rankValue-1
ORDER BY (DATEDIFF(ms,t1.create_date,t2.create_date))/60000.0 DESC
Scott PletcherSenior DBACommented:
I would think DENSE_RANK would create duplicate output.

I suggest ROW_NUMBER instead.
cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM tablename
    c1.<col>, c1...., CAST(DATEDIFF(SECOND, c2.TimeStamp, c1.TimeStamp) / 60.0 AS int) AS MinutesLater
FROM cte c1
    c2.row_num = c1.row_num - 1

Open in new window

select T1.ID, COALESCE(T1.TimeStamp -
(SELECT TOP 1 T2.TimeStamp FROM YourTable T2 WHERE T2.ID< T1.ID ORDER BY T2.ID DESC) , 0) AS Gap
FROM YourTable T1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

just for clarity...

are you implying any requirement to the sequencing?

e.g. previous row id  = current row id - 1
or just the numerical sequence order.... of the rows..

what values do you want the first and last row (of a sequence ?) to show as the difference

Scott PletcherSenior DBACommented:
As I understood from the original q:

>> The order would be providd by the ID column. <<
mgordon-spiAuthor Commented:
Hi All,

Very sorry for the delay. Chirstmas has made things crazy here, I'll test these in the next day and come back to you.

The ID does provide the sequence, however it is possible that ID's could be missing if records were deleted, I guess RowNumber would be better off in this instance?
gaps in ID's won't matter in my SQL, it seeks out the top ID prior to the ID we are calculating the gap against.  This could be 1 less or 10000 less, it wouldn't matter.  Just as long as id sequence reflects time sequence
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.