Grouping the records on the time difference

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

SQL Server 2005 Forums

Transact-SQL (2005)

Grouping the records on the time difference

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-14 : 12:36:20

Hi Guys,

I want to group the records on the time difference

declare @tbl as table(id int,intid int,val int,dt datetime)
insert into @tbl
select 1,1,10,'03/31/2006 15:05:22' union all
select 2,1,12,'03/31/2006 15:10:22' union all
select 3,1,15,'03/31/2006 15:15:22' union all
select 4,1,12,'03/31/2006 15:25:22' union all
select 5,1,8,'03/31/2006 15:30:22' union all
select 6,1,6,'03/31/2006 15:35:22' union all
select 7,1,4,'03/31/2006 15:40:22' union all
select 8,1,3,'03/31/2006 15:45:22' union all
select 9,1,10,'03/31/2006 15:50:22'

declare @tbl1 as table(intid int,Tm int,val int)
insert into @tbl1
select 1,5,10

I want a output such that when the val in @tbl goes below the val in @tbl1 for the Tm mentioned in @tbl1
then the time difference should be shown.For example record 1 it starts with 10 the records remain more than 10 till
record number 5.From 5 the records remains lower than 10 till record number 9.So I need to show the the time
difference from record number 5 till 9.
But there is a catch.In @tbl1 there is column named Tm.The time difference sould be calculated only if the diff
more than Tm value in @tbl1.
For example if the value of Tm is changed to say 25 then the there is no need to show the time difference since the
time difference value from record 5 to record 9 is less than 25.
Hope I am clear.

Please help me out.

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 12:56:19

So you do need to compare values to 10 i.e first value every time rather than 12 from where it starts dipping?

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-14 : 13:02:49

Yes the value to be checked will be mentioned in val of @tbl1.Any record whose value goes below val from that time till the next time when it goes above the value of val,the time difference should be calculated.But if the calculated time diff is less than the Tm value of @tbl1 the time diff should be ignored.
Hope I am clear on this.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-14 : 14:51:46

Guys anyone.
Please help me out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources