March 10, 2010 at 9:30 am
I have a table having data
ID Machine StartTime
1 M1 2010-03-10 15:00:00
2 M2 2010-03-10 15:10:00
3 M3 2010-03-10 15:20:00
4 M4 2010-03-10 15:30:00
I want a query to show output like this ( time dIfference between row and previous row)
ID Machine StartTime MinDifference
1 M1 2010-03-10 15:00:00 00
2 M2 2010-03-10 15:10:00 10
3 M3 2010-03-10 15:20:00 10
4 M4 2010-03-10 15:30:00 10
Help me
March 10, 2010 at 10:00 am
Edit -- Post went to wrong thread.
March 10, 2010 at 10:13 am
This one is actually pretty simple:
create table dbo.MyTest1 (
ID int,
Machine char(2),
StartTime datetime
);
insert into dbo.MyTest1
select 1, 'M1', '2010-03-10 15:00:00' union all
select 2, 'M2', '2010-03-10 15:10:00' union all
select 3, 'M3', '2010-03-10 15:20:00' union all
select 4, 'M4', '2010-03-10 15:30:00';
select
mt1.ID,
mt1.Machine,
mt1.StartTime,
isnull(datediff(mi, mt2.StartTime, mt1.StartTime),0) as Diff
from
dbo.MyTest1 mt1
left outer join dbo.MyTest1 mt2
on (mt1.ID = mt2.ID + 1);
March 10, 2010 at 11:19 am
Thanks for your reply
However for test i have given ID in serial number
Let us say I have data like this then wht will the query?
ID Machine StartTime
100 M1 2010-03-10 15:00:00
2 M2 2010-03-10 15:10:00
30 M3 2010-03-10 15:20:00
4 M4 2010-03-10 15:30:00
March 10, 2010 at 11:53 am
Well, then you'd just have to create your own serialized number using ROW_NUMBER.
Based on Lynns sample data it would look like
;WITH cte AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY StartTime) AS ROW,
ID,
Machine,
StartTime
FROM
dbo.MyTest1 mt1
)
SELECT
mt1.ID,
mt1.Machine,
mt1.StartTime,
ISNULL(DATEDIFF(mi, mt2.StartTime, mt1.StartTime),0) AS Diff
FROM
cte mt1
LEFT OUTER JOIN cte mt2
ON (mt1.row = mt2.row + 1);
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply