Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: Finding the highest current number only for an increasing range

    --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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2012
    Posts
    2

    Finding the highest current number only for an increasing range

    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.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jan 2012
    Posts
    84
    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)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •