Time difference
-
How to calculate time difference between up and down rows?
So, I need to calculate between 15:09:00 and 06:43:00 to get 08:26.
And so on for other rows..
-
Look at the Lag function. That should probably work.
https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2017
-
You should check how to post questions with consumable data before posting a question:
;WITH CTE AS
(
SELECT * FROM (VALUES
(34,1,1003,convert(time,'06:43:00'),convert(date,'01.04.2019'),101),
(35,1,1003,'15:09:00','01.04.2019',101),
(50,10,1003,'06:43:00','10.04.2019',101),
(51,10,1003,'15:08:00','10.04.2019',101)
) t(id,dan,pin,Vrjeme,datum,odjel)
)
SELECT *,
DATEADD(mi,DATEDIFF(mi,LAG(Vrjeme) OVER (PARTITION BY datum ORDER BY datum,Vrjeme),Vrjeme),convert(time,'00:00:00')) diff
FROM CTE -
Unless I'm misreading the given solution, it won't properly calculate the time difference correctly for entries that span a date boundary.
This is one of the many reasons why I rail against the mistake of storing dates and times in separate columns. It's just not worth it in the long run.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff Moden wrote:
Unless I'm misreading the given solution, it won't properly calculate the time difference correctly for entries that span a date boundary. This is one of the many reasons why I rail against the mistake of storing dates and times in separate columns. It's just not worth it in the long run.
Yes, it won't. It was more just a demonstration of how to post consumable data and use the LAG function. The SQL gets a bit long-winded if you start having to do:
convert(datetime,datum)+convert(datetime,Vrjeme)
and have those inside a LAG function and I couldn't work up the effort or enthusiasm to add that to the solution. You're welcome to add it if you feel the urge.
-
Ah... sorry, Jonathan... If that came across as me badmouthing your code, it wasn't what was intended, especially since the OP provided no data to suggest that a daily boundary would be crossed. I really just wanted the OP to know what the limitation was and the sidebar that I consider separate date and time columns to be a serious mistake in most cases.
As you pointed out, it would also be helpful if the OP posted readily consumable data rather than a picture so that none of us would have had to type the data in from a picture.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply