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?
mgordon-spiAsked:
Who is Participating?
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.

TempDBACommented:
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,
           t1.TimeStamp,
           t1.Value,
           (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
0
Scott PletcherSenior DBACommented:
I would think DENSE_RANK would create duplicate output.

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

Open in new window

0
deightonprogCommented:
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
0

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LowfatspreadCommented:
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

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

>> The order would be providd by the ID column. <<
0
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?
0
deightonprogCommented:
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
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.