November 27, 2014 at 2:49 am
Hi,
I have a table as per below, I want a new field called "TimeTaken" which will calculate the time it has taken against the previous Id
So for the Id 1 the new field would show a value "03:00:00" because the time difference
between
Id 1 "2014-11-17 08:26:45.600"
and
Id 2 "2014-11-17 11:26:45.600"
is three hours "03:00:00"
Record Id 2 will have a value of "24:04:00" as it taken 24 hours and four minutes when you compare Id 2 to Id 3
Record Id 10 will have a empty value as that is the last row.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RunningTime](
[Id] [int] NOT NULL,
[TimeStamp] [datetime] NOT NULL,
CONSTRAINT [PK_RunningTime] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into RunningTime values (1,'2014-11-17 08:26:45.600')
insert into RunningTime values (2,'2014-11-17 11:26:45.600')
insert into RunningTime values (3,'2014-11-18 11:26:49.600')
insert into RunningTime values (4,'2014-11-19 05:29:45.600')
insert into RunningTime values (5,'2014-11-19 05:40:45.600')
insert into RunningTime values (6,'2014-11-23 19:26:45.600')
insert into RunningTime values (7,'2014-11-26 23:26:45.600')
insert into RunningTime values (8,'2014-11-27 00:59:45.600')
insert into RunningTime values (9,'2014-11-27 05:29:45.800')
insert into RunningTime values (10,'2014-11-27 09:26:45.600')
Thanks
November 27, 2014 at 3:31 am
Here's a bit of code to get you started:-
SELECT
ID,
TimeStamp,
LAG(TimeStamp,1) OVER (ORDER BY ID DESC),
DATEDIFF(HOUR, TimeStamp, LAG(TimeStamp,1) OVER (ORDER BY ID DESC))
FROM [dbo].[RunningTime]
It uses the LAG function to get the previous value and then uses the DATEDIFF function to compare the two.
November 27, 2014 at 3:51 am
Here is one way of doing it:
With MyCTE as(
select RT1.*, RT2.TimeStamp as EndTimeStamp,datediff(second,RT1.TimeStamp,RT2.TimeStamp) as DiffInSeconds
from RunningTime RT1 LEFT JOIN RunningTime RT2 ON RT1.Id = RT2.Id - 1)
select *,
right ('000' + CAST (DiffInSeconds / 60 /60 as varchar(4)), 3) -- Hours part
+ ':' + right ('0' + CAST (DiffInSeconds / 60 % 60 as varchar(2)),2) --Minutes part
+ ':' + right ('0' + CAST (DiffInSeconds % 60 as varchar(2)),2) --seconds part
from MyCTE
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 27, 2014 at 4:18 am
select
t1.id
,t1.timestamp
,t2.timestamp
,TimeTaken = cast(datediff(second,t1.timestamp,t2.timestamp)/3600 as varchar)+':'
+right('0'+cast(datediff(second,t1.timestamp,t2.timestamp)/60 %60 as varchar),2)+':'
+right('0'+cast(datediff(second,t1.timestamp,t2.timestamp) %60 as varchar),2)
from dbo.runningtime t1
left join dbo.runningtime t2 on t2.id = t1.id+1
Here's another attempt.
@dba-2 From The Cold, I don't mean to pick holes but LAG isn't available in 2008.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
November 27, 2014 at 4:28 am
BWFC (11/27/2014)
selectt1.id
,t1.timestamp
,t2.timestamp
,TimeTaken = cast(datediff(second,t1.timestamp,t2.timestamp)/3600 as varchar)+':'
+right('0'+cast(datediff(second,t1.timestamp,t2.timestamp)/60 %60 as varchar),2)+':'
+right('0'+cast(datediff(second,t1.timestamp,t2.timestamp) %60 as varchar),2)
from dbo.runningtime t1
left join dbo.runningtime t2 on t2.id = t1.id+1
Here's another attempt.
@dba-2 From The Cold, I don't mean to pick holes but LAG isn't available in 2008.
Fair enough but the OP didn't specify a version so I assumed SQL Server 2012.
November 27, 2014 at 4:30 am
It's a 2008 forum so I took a guess on that 😉
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
November 27, 2014 at 4:35 am
I would suggest a slight modification to the above, since the previous ID is not always going to be the current ID minus 1 (there will be gaps). Also, the date manipulation can be made a bit simpler.
Here it is, not tested, though:
WITH IDs AS (
select ROW_NUMBER() OVER (ORDER BY t.id) RowNo
,t1.timestamp
,t2.timestamp
from dbo.runningtime
)
SELECT TimeTaken = cast(t2.timestamp - t1.timestamp as time)
FROM IDs t1
left join IDs t2 on t2.RowNo = t1.RowNo+1
John
November 27, 2014 at 4:49 am
BWFC (11/27/2014)
It's a 2008 forum so I took a guess on that 😉
D'oh :pinch:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply