maximum function 2

[dswitzer]

SQL2000.
I have a dataset:

seqNum value
1 30.25
2 31.25
3 32.25
4 30.00
5 28.30
6 29.50
7 28.00
8 28.00
9 28.00
10 28.00
11 28.00



I am trying to write a sql statement that returns each record + checks 5 sequence #'s forward and returns the maximum value.

So, my result set should be:

seqNum value max5DayValue
1 30.25 32.25
2 31.25 32.25
3 32.25 32.25
4 30.00 30.00
5 28.30 29.50
6 29.50 29.50

Not sure why I'm stumped - seems straightforward enough...I hope I can avoid a cursor...

 if exists (select id from tempdb..sysobjects where id=object_id('tempdb..#tbl1'))
 	DROP table #tbl1
select 1 as seqNum,30.25 as value into #tbl1 
insert into #tbl1 select 2,32.25
insert into #tbl1 select 3,32.25
insert into #tbl1 select 4,30.00
insert into #tbl1 select 5,28.30
insert into #tbl1 select 6,29.50
insert into #tbl1 select 7,28.00
insert into #tbl1 select 8,28.00
insert into #tbl1 select 9,28.00
insert into #tbl1 select 10,28.00
insert into #tbl1 select 11,28.00
select * from #tbl1 order by seqNum

Thanks.

 

[gmmastros]

>> I hope I can avoid a cursor...

I think you can avoid a cursor.

 [COLOR=blue]if[/color] exists ([COLOR=blue]select[/color] id [COLOR=blue]from[/color] tempdb..sysobjects [COLOR=blue]where[/color] id=[COLOR=#FF00FF]object_id[/color]([COLOR=red]'tempdb..#tbl1'[/color]))
     [COLOR=blue]DROP[/color] [COLOR=blue]table[/color] #tbl1
[COLOR=blue]select[/color] 1 [COLOR=blue]as[/color] seqNum,30.25 [COLOR=blue]as[/color] [COLOR=blue]value[/color] [COLOR=blue]into[/color] #tbl1
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #tbl1 [COLOR=blue]select[/color] 2,32.25
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #tbl1 [COLOR=blue]select[/color] 3,32.25
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #tbl1 [COLOR=blue]select[/color] 4,30.00
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #tbl1 [COLOR=blue]select[/color] 5,28.30
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #tbl1 [COLOR=blue]select[/color] 6,29.50
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #tbl1 [COLOR=blue]select[/color] 7,28.00
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #tbl1 [COLOR=blue]select[/color] 8,28.00
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #tbl1 [COLOR=blue]select[/color] 9,28.00
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #tbl1 [COLOR=blue]select[/color] 10,28.00
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #tbl1 [COLOR=blue]select[/color] 11,28.00

[COLOR=blue]select[/color] 	A.*, [COLOR=#FF00FF]Max[/color](B.Value) [COLOR=blue]As[/color] B_Value
[COLOR=blue]from[/color] 	#tbl1 [COLOR=blue]As[/color] A
		[COLOR=blue]Inner[/color] [COLOR=blue]join[/color] #tbl1 [COLOR=blue]As[/color] B
			[COLOR=blue]On[/color] B.seqNum between A.seqNum and A.seqNum + 4
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] A.SeqNum, A.Value
[COLOR=blue]order[/color] [COLOR=blue]by[/color] A.seqNum

There are some interesting things about this query. If there's anything you don't understand, let me know and I will explain it for you.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Qik3Coder]

Snap!

You may have to giggle the between values for what you want.

Select t1.seqNum, t1.value, max(t2.value) as FiveDayValue 
from #tbl1 t1
Join #tbl1 t2 ON
	t1.seqNum between t2.seqNum-5 and t2.seqNum+5
group by t1.seqNum, t1.value
order by t1.seqNum

-Sometimes the answer to your question is the hack that works

 

[dswitzer]

Thanks guys - that does the trick.