Calculate Differences Between Records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculate Differences Between Records

Author

Topic

Teaman
Starting Member

4 Posts

Posted - 2015-02-09 : 17:56:22

Hi all,
I am a new to SQL. I have been struggling to find a solution to this problem and was wondering if anyone can help.

I would like to calculate the date difference between two records:

ID Start End
1 2/9/15 13:28:41 2/9/15 13:28:46
2 2/9/15 13:29:03 2/9/15 13:29:12
3 2/9/15 13:29:17 2/9/15 13:29:21
4 2/9/15 13:29:27 2/9/15 13:29:31

So I need to find the difference between two consecutive records and put the result in a new column called Downtime. For example:

Record 2 Start (2/9/15 13:29:03) - Record 1 End (2/9/15 13:28:46) = 0:00:17
Record 3 Start (2/9/15 13:29:17) - Record 2 End (2/9/15 13:29:12) = 0:00:05
Record 4 Start (2/9/15 13:29:27) - Record 3 End (2/9/15 13:29:21) = 0:00:06
and so on…

Also what do I do about the 1st record since there is no previous record to subtract from?

So far I have this code in my query to generate my table: SELECT Start, End FROM group_table3

but I do know how accomplish the rest. Any help on this matter would be greatly appreciated.

My desired output should look like this:
ID Start End Downtime
1 2/9/15 13:28:41 2/9/15 13:28:46 #VALUE!
2 2/9/15 13:29:03 2/9/15 13:29:12 0:00:17
3 2/9/15 13:29:17 2/9/15 13:29:21 0:00:05
4 2/9/15 13:29:27 2/9/15 13:29:31 0:00:06

Sam Smith

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-09 : 18:56:42

If there are no gaps in id sequence:

select a.Start
      ,a.End
      ,convert(varchar(8),dateadd(second,datediff(second,b.Start,a.End),0),114) as downtime
  from group_table3 as a
       left outer join group_table3 as b
                    on b.ID=a.ID-1

If there are gaps:

with cte
  as (select *
            ,row_number() over(order by ID) as rn
        from group_table3
     )
select a.Start
      ,a.End
      ,convert(varchar(8),dateadd(second,datediff(second,b.Start,a.End),0),114) as downtime
  from cte as a
       left outer join cte as b
                    on b.rn=a.rn-1

Teaman
Starting Member

4 Posts

Posted - 2015-02-10 : 12:21:01

quote:

Originally posted by bitsmed

If there are no gaps in id sequence:

select a.Start
      ,a.End
      ,convert(varchar(8),dateadd(second,datediff(second,b.Start,a.End),0),114) as downtime
  from group_table3 as a
       left outer join group_table3 as b
                    on b.ID=a.ID-1

If there are gaps:

with cte
  as (select *
            ,row_number() over(order by ID) as rn
        from group_table3
     )
select a.Start
      ,a.End
      ,convert(varchar(8),dateadd(second,datediff(second,b.Start,a.End),0),114) as downtime
  from cte as a
       left outer join cte as b
                    on b.rn=a.rn-1

Thank you bitsmed! It worked perfectly!

Sam Smith

pradeepbliss
Starting Member

28 Posts

Posted - 2015-02-13 : 02:07:10

declare @i int
declare @j datetime
declare @k datetime
declare @n int
select @n=MAX(id) from #YourTableName
set @i = 1
while @i <= @n
begin
Select @i= ID from #YourTableName where id=@i
if @i = 1
begin
select '#value!' as '[hh:mm:ss]'
select @j = enddate from #YourTableName where id = @i--enddate
end
else
begin
select @j = enddate from #YourTableName where id =@i - 1
select @k=startdate from #YourTableName where id=@i--startdate
Select CAST((@j-@k) as time(1)) '[hh:mm:ss]'
end
set @i=@i+1
end

pradeepbliss
Starting Member

28 Posts

Posted - 2015-02-13 : 03:13:41

Select CAST((TD1.enddate - TD.startdate) as time(1)) '[hh:mm:ss]'
from @YourTableName TD
inner join @YourTableName TD1 on TD.id=TD1.Id
where Td.id != (select MAX(id) from @YourTableName)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources