Finding the highest current number only for an increasing range

pinkfootball · #1 (**permalink**) 01-26-12, 19:01

--sorry about the formatting; this is supposed to be two columns!
RecordNo Speed
-------- -----
1 0
2 0
3 0
4 0
5 23
6 66
7 48
8 0
9 31
10 0
11 34
12 23

The above data shows the speed of vehicle over a time period, given the above data I need to achieve the result below:

--sorry about the formatting; this is supposed to be three columns!
RecordNo Speed LastAcceleration
-------- ----- ----------------
1 0 0
2 0 0
3 0 0
4 0 0
5 23 23
6 66 66
7 48 66
8 0 66
9 31 31
10 0 31
11 34 34
12 23 34

The code below is almost there but falls over on Recordno 8:

select
curr.recordno,curr.speed
,CASE WHEN curr.speed >= ISNULL(prev.speed,0) THEN curr.speed
ELSE (
SELECT MAX(speed) FROM speedtest
WHERE recordno between (CASE WHEN curr.speed >= prev.speed then curr.recordindex else prev.recordno end ) and curr.recordno
)
END as LastAcceleration

From speedtest prev RIGHT JOIN speedtest curr
on prev.vrm = curr.vrm
and prev.recordno+1 = curr.recordno
order by curr.recordno

I think I've been staring at this one too long. I've tried self-joins with correlated sub-queries but think I'm missing something obvious? This is for a 2008 project so doesn't have to work with any versions prior to that. Any help would be appreciated.

Pat Phelan · #2 (**permalink**) 01-26-12, 19:32

Code:

CREATE TABLE #foo (
   RecordNo		INT			NOT NULL
,  Speed		INT			NOT NULL
   )

INSERT INTO #foo (RecordNo, Speed)
   SELECT        1, 0
   UNION SELECT  2, 0
   UNION SELECT  3, 0
   UNION SELECT  4, 0
   UNION SELECT  5, 23
   UNION SELECT  6, 66
   UNION SELECT  7, 48
   UNION SELECT  8, 0
   UNION SELECT  9, 31
   UNION SELECT 10, 0
   UNION SELECT 11, 34
   UNION SELECT 12, 23

SELECT
   this.RecordNo
,  this.Speed
,  CASE
      WHEN 0 = prev.Speed THEN this.Speed
      WHEN prev.Speed IS NULL THEN 0
      ELSE prev.Speed
   END AS [Last Acceleration]
   FROM #foo AS this
   LEFT JOIN #foo AS prev
      ON (prev.RecordNo = this.RecordNo - 1)

DROP TABLE #foo

-PatP

pinkfootball · #3 (**permalink**) 01-27-12, 03:55

Hi Pat

Thank you for giving up your time to help me out. Unfortunately your solution doesn't quite match the resultset that I'm after. The LastAcceleration for RecordNo 6 should be 66 and not 23 for example.

Wim · #4 (**permalink**) 01-27-12, 06:35

I don't understand the business logic.

Code:

RecordNo	Speed	LastAcceleration
4		0	0	-- no speed => 0, OK
5		23	23	-- accelerating from speed 0 to 23 => 23, OK
6		66	66	-- accelerating from speed 23 to 66 => 43, NOK
7		48	66	-- decelerating from speed 66 to 48 => -18, NOK
8		0	66	-- decelerating from speed 48 to 0 => -48, NOK

What's the logic behind the results you expect?

kordirko · #5 (**permalink**) 01-27-12, 14:05

Code:

go
with src as (
   SELECT        1 recordno, 0 speed
   UNION SELECT  2, 0
   UNION SELECT  3, 0
   UNION SELECT  4, 0
   UNION SELECT  5, 23
   UNION SELECT  6, 66
   UNION SELECT  7, 48
   UNION SELECT  8, 0
   UNION SELECT  9, 31
   UNION SELECT 10, 0
   UNION SELECT 11, 34
   UNION SELECT 12, 23
),
brumba( rn, speed, maxspeed )
as (
	select recordno, speed, speed
	from src where recordno = 1
	union all
	select f.RecordNo, f.speed,
		   case when f.Speed > b.speed 
		        then f.Speed 
		        else b.maxspeed 
		   end
	 from src f join brumba b on f.RecordNo = b.rn + 1
)
select * from brumba;


rn          speed       maxspeed
----------- ----------- -----------
1           0           0
2           0           0
3           0           0
4           0           0
5           23          23
6           66          66
7           48          66
8           0           66
9           31          31
10          0           31
11          34          34
12          23          34

(12 row(s) affected)

Internet Services

Web Development

Digital Marketing

Consumer Tech