CODEWITHSUNDEEP
sqlsql-server-2008t-sqlrdbms
Jack
Jack

Reputation: 7557

Efficiently writing this formula in SQL server 2008

Say I have table and these are its sample rows

ChangeID    Change
   1         102
   2         105
   3         107
   4         110

The change formula is 

(CurrentRowChange - PreviousRowChange) / PreviousRowChange

Hence:

and so on. How can I efficiently write this formula in SQL?

I know I can write a scalar function and then do a RowNumber and order By ChangeID and fetch the row number's Change value and then find the current row number - 1 and then fetch that row's Change value and do a divide.

Is there any better way to achieve this?

Upvotes: 1

Views: 420

Answers (4)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

select T1.ChangeID,
       (1.0 * T1.Change / T2.Change) - 1 as Result
from TableName as T1
  outer apply (
              select top(1) T.Change
              from TableName as T
              where T.ChangeID < T1.ChangeID
              order by T.ChangeID desc
              ) as T2

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

While the ChangeID's are sequential in the sample, I wouldn't assume that they always are. So I would do something like this:

 with RankedIDs as
 select ChangeID
 , Change
 , rank() over
 (partition by ChangeID order by ChangeId) rank
 where something maybe ;

 select case
 when r1.rank = 1 then 0
 else (r1.change - r2.change) / r2.change
 end SomeName
 from RankedIds r1 join RankedIds r2 on r1.rank = r2.rank + 1

That's the basic idea. You might want to add divide by zero protection

Upvotes: 0

Andomar
Andomar

Reputation: 238196

select  cur.*
,       case
        when prev.ChangeId is null then 0
        else 1.0 * (cur.Change - prev.Change) / prev.Change
        end
from    Table1 cur
left join
        Table1 prev
on      cur.ChangeId = prev.ChangeId + 1

SQL Fiddle example.

Upvotes: 4

John Woo
John Woo

Reputation: 263803

give this a try, assuming that CHANGEID can be deleted and it is IDENTITY.

WITH changeList
AS
(
    SELECT ChangeID, [Change],
           (ROW_NUMBER() OVER (ORDER BY ChangeID ASC)) -1 AS rn
    FROM   TableName
),
normalList
AS
(
    SELECT ChangeID, [Change],
           (ROW_NUMBER() OVER (ORDER BY ChangeID ASC)) AS rn
    FROM   TableName
)
SELECT a.ChangeID, a.[Change], 
       COALESCE((a.Change - b.change) / (b.change * 1.0),0) result
FROM   changeList a
       LEFT JOIN normalList b
          ON a.rn = b.rn

Upvotes: 4

Related Questions