Time difference between consecutive date stamps

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

Time difference between consecutive date stamps

Author

Topic

Lcarter
Starting Member

4 Posts

Posted - 2011-03-24 : 18:09:27

Hi, I'm new here and have been doing some SQL for awhile. I'm bangingbmy head on the following. Can I get some assistance.

CODE
Badge. Tagname     TimeStamp
231228    X1879796000    2011-03-08 06:00:00.000
231228    X1879796000    2011-03-08 07:00:00.000
231228    X1879796000    2011-03-08 10:00:00.000
231228    X1879796000    2011-03-08 17:00:00.000
231229    X1885506800    2011-03-08 06:01:00.000
231229    X1887174400    2011-03-08 06:03:00.000
231231    X1890189900    2011-03-08 06:05:30.000
231231    X1890189900    2011-03-08 15:05:30.000

Expected results example
Badge. Tagname. Start Datetime End Datetime Sec diff
231228 X1879796000. 2011-03-08 06:00:00.000. 2011-03-08 07:00:00.000. 360
231228 X1879796000. 2011-03-08 10:00:00.000. 2011-03-08 17:00:00.000. 25200
231229    X1885506800    2011-03-08 06:01:00.000  2011-03-08 06:03:00.000. 120

This is a what I have worked on.

; With RankingData As
(
  Select tagname, badge, datetime,
          Row_Number() Over (Partition By badge Order By datetime) As RowId
  From    @ts
)
Select A.Badge,
       Max(B.Datetime) As tsmax,
       Min(A.Datetime) As tsmin,
       DateAdd(Second, Min(DateDiff(Second, A.Datetime, B.Datetime)), 0) as tsdiff_min,
       DateAdd(Second, Max(DateDiff(Second, A.Datetime, B.Datetime)), 0) As tsdiff_max
From   RankingData As A
       Inner Join RankingData As B
         On A.Badge = B.Badge
         And A.RowId = B.RowId - 1
Group BY A.Badge

Can someone help.

Thanks

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-03-24 : 20:20:22

I went old school with this sql2000 but it works fine.

select 231228 as badge,1879796000 as tagname,cast('2011-03-08 06:00:00.000' as datetime) as timestamp
into #tmppmd
insert into #tmppmd values (231228,1879796000,'2011-03-08 07:00:00.000')
insert into #tmppmd values (231228,1879796000,'2011-03-08 10:00:00.000')
insert into #tmppmd values (231228,1879796000,'2011-03-08 17:00:00.000')
insert into #tmppmd values (231229,1885506800,'2011-03-08 06:01:00.000')
insert into #tmppmd values (231229,1887174400,'2011-03-08 06:03:00.000')
insert into #tmppmd values (231231,1890189900,'2011-03-08 06:05:30.000')
insert into #tmppmd values (231231,1890189900,'2011-03-08 15:05:30.000')

create table #tmppmd2 (idt int identity(1,1), badge int,tagname int, timestamp datetime)
insert into #tmppmd2 (badge,tagname,timestamp)
select pmd.*
from #tmppmd pmd
order by badge,timestamp

select pmd.badge,pmd.tagname,pmd.timestamp as StartTimeStamp,pmd2.timestamp as EndTimeStamp,datediff(ss,pmd.timestamp,pmd2.timestamp) as Secdiff
from #tmppmd2 pmd
left join #tmppmd2 pmd2 on pmd2.idt = pmd.idt + 1
where pmd.idt % 2 <> 0

Lcarter
Starting Member

4 Posts

Posted - 2011-03-25 : 07:26:28

Thanks i tried your solution and could not work it our. my table does not have this "idt" field your qry refers to.

select idt???, pmd.value,pmd.tagname,pmd.datetime as StartTimeStamp,pmd2.datetime as EndTimeStamp,datediff(ss,pmd.datetime,pmd2.datetime) as Secdiff
from StringHistory pmd
left join StringHistory pmd2 on pmd2.RowId = pmd.RowId + 1
where pmd.RowId % 2 <> 0
and pmd.tagname = 'mytag'
and pmd.datetime >= '03/24/2011 04:10:00'
and pmd.datetime <= '03/24/2011 19:50:00'
and pmd.Value = '11111'

how would i incorporate the idt into my table (i renamed timestamp to datetime)? my table has just badge tagname & timestamp and the table design cannot be changed.

thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-25 : 08:02:58

I couldn't duplicate your expected results, but this may help

; With RankingData As
(
Select tagname, badge, tStamp,
Row_Number() Over (Partition By badge Order By tStamp) As RowId
From @table
)

select rd1.Badge,rd1.tagname ,rd1.tStamp as StartTime,rd2.tStamp as EndTime
,datediff(s,rd1.tStamp,rd2.tStamp) as Diff
from RankingData rd1
inner join RankingData rd2
on
rd1.badge = rd2.badge

and rd1.rowid = rd2.rowid - 1

Jim

Everyday I learn something that somebody else already knew

Lcarter
Starting Member

4 Posts

Posted - 2011-03-25 : 08:24:04

hi,
This is the results i receive.
badge tagname start time stamp end time stamp
57370 tagname2_Dat_badge 2011-03-24 10:09:38.2370000 2011-03-24 10:27:39.2840000 1081
57370 tagname2_Dat_badge 2011-03-24 10:27:39.2840000 2011-03-24 11:04:28.6100000 2209 - do not need this one
57370 tagname2_Dat_badge 2011-03-24 11:04:28.6100000 2011-03-24 11:10:26.1600000 358
57370 tagname2_Dat_badge 2011-03-24 14:49:03.3320000

here is my raw data..
tagname badge timestamp
tagname2_Dat_badge 57370 2011-03-24 10:09:38.2370000
tagname2_Dat_badge 57370 2011-03-24 10:27:39.2840000
tagname2_Dat_badge 57370 2011-03-24 11:04:28.6100000
tagname2_Dat_badge 57370 2011-03-24 11:10:26.1600000
tagname2_Dat_badge 57370 2011-03-24 14:49:03.3320000

expected results
tagname badge start time stamp end time stamp second time diff
tagname2_Dat_badge 57370 2011-03-24 10:09:38.2370000 - 2011-03-24 10:27:39.2840000
tagname2_Dat_badge 57370 2011-03-24 11:04:28.6100000 - 2011-03-24 11:10:26.1600000
tagname2_Dat_badge 57370 2011-03-24 14:49:03.3320000 - This one would have no end time stamp or sec diff since only one start stamp.

Sorry if im not communicating myself clearly.

Lcarter
Starting Member

4 Posts

Posted - 2011-03-25 : 12:39:44

Just wanted to say Thanks for your time.

I managed to do some creative looping in Coldfusion to accomplish my needs. I ended up using the following and indexing through each badge number.
It is a little slow the bottle neck is "CF" not SQL.

create table #tmppmd2 (idt int identity(1,1), badge int,tagname int, timestamp datetime)
insert into #tmppmd2 (badge,tagname,timestamp)
select pmd.*
from #tmppmd pmd
where badge = '#badge#' -- loop through all badges in CF
order by badge,timestamp

select pmd.badge,pmd.tagname,pmd.timestamp as StartTimeStamp,pmd2.timestamp as EndTimeStamp,datediff(ss,pmd.timestamp,pmd2.timestamp) as Secdiff
from #tmppmd2 pmd
left join #tmppmd2 pmd2 on pmd2.idt = pmd.idt + 1
where pmd.idt % 2 <> 0

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-03-25 : 15:32:31

The IDT column was created in the #tmppmd2 table which is your temporary working table

create table #tmppmd2 (idt int identity(1,1), badge int,tagname int, timestamp datetime)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources