Help on writing a query - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Help on writing a query

Author

Topic

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2011-05-13 : 14:27:21

Here is my question for you.

I have a table with 2 columns. One Column Contains TimeStamps and the other a counter. I need to find the TimeStamps, which has its counter greater than both the time stamp before it and after it.

eg.


12/Apr/2010 2:22:13  400
12/Apr/2010 2:23:13  500
12/Apr/2010 2:25:15  300
12/Apr/2010 2:26:13  100
12/Apr/2010 2:28:15  200
12/Apr/2010 2:29:15  100
12/Apr/2010 2:30:15  200

if the data is as above the non strike through records below are those, I need to get.


12/Apr/2010 2:22:13  400
12/Apr/2010 2:23:13  500
12/Apr/2010 2:25:15  300
12/Apr/2010 2:26:13  100
12/Apr/2010 2:28:15  200
12/Apr/2010 2:29:15  100
12/Apr/2010 2:30:15  200

ie. The time before 12/Apr/2010 2:23:13, the count is less than 500 and the time after 12/Apr/2010 2:23:13, the count is less than 500.

And it is same in case of 12/Apr/2010 2:28:15

For any other set of 3 consecutive timestamps, the middle one has a value higher than both sides.

Here is the code to generate some random data.


Declare @T DateTime, @Val int, @i int, @Val1 int


Create table #hc (dt datetime, hits int)
-- Drop table #hc
Set @i = 0
Set @T = convert(varchar(17),getdate())
	

While @i < 100
Begin
	Set @T = DATEADD(minute,1,@T)
	Set @Val = 1000 * rand()
	Set @Val1 = 100 * rand()

	if @Val/100 <>  @Val1/10 -- (this is done to get some 1 minute difference skipped)
	Begin
		insert into #hc 
		Select @T, @Val;
		Set @i = @i + 1
	End
End


-- here is the data containg table 
Select convert(varchar(17),dt) as dt, hits from #hc

Thanks

Srinika

Andrew Zwicker
Starting Member

11 Posts

Posted - 2011-05-14 : 01:48:06

Hi,

This sql should work. I tested it using SQL Server 2005, but it should work in 2000 as well.

Essentially what I'm doing here is first coming up with a mapping from one date to the next, to help with ordering the dates. Then I'm using that mapping to help with the comparisons.


select curCounter.dt, curCounter.hits from 
(
	select dt1.dt as 'curTime', max(dt2.dt) as 'prevTime' from #hc dt1 
	inner join #hc dt2 on dt1.dt > dt2.dt 
	group by dt1.dt
)
infoCurToNext
inner join 
(
	select dt1.dt as 'curTime', max(dt2.dt) as 'prevTime' from #hc dt1 
	inner join #hc dt2 on dt1.dt > dt2.dt 
	group by dt1.dt
)
infoPrevToCur on infoCurToNext.prevTime = infoPrevToCur.curTime
inner join #hc prevCounter on prevCounter.dt = infoPrevToCur.prevTime
inner join #hc curCounter on curCounter.dt = infoPrevToCur.curTime -- same as infoCurToNext.prevTime
inner join #hc nextCounter on nextCounter.dt = infoCurToNext.curTime 
where curCounter.hits > prevCounter.hits and curCounter.hits > nextCounter.hits

For a real source table [instead of a temp table], I recommend turning the following logic into a view [and using the view with the main query]:


	select dt1.dt as 'curTime', max(dt2.dt) as 'prevTime' from SourceTable dt1 
	inner join SourceTable dt2 on dt1.dt > dt2.dt 
	group by dt1.dt

I hope this helps.

Visit http://www.helpwithsql.com

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2011-05-16 : 11:52:08

Thankyou so much Andrew
This Query works well for me.

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources