CODEWITHSUNDEEP
sqlsql-server-2012
user3242205
user3242205

Reputation: 89

calculating percent change over time

I've created the structure and sample data here. I'm not sure how to go about calculating the change over time.

My desired result set is:

a   | % growth
abc | 4.16
def | 0.83
hig | -0.2

The % change being (last value - first value) / days:

a   | % growth
abc | (30-5) / 6
def | (6-1) / 6
hig | (4-5) / 5

I'm trying:

SELECT a.*, 
       b.val, 
       c.val 
FROM   (SELECT a, 
               Min(dt) AS lowerDt, 
               Max(dt) AS upperDt 
        FROM   tt 
        GROUP  BY a) a 
       LEFT JOIN tt b 
              ON b.dt = a.lowerdt 
                 AND b.a = a.a 
       LEFT JOIN tt c 
              ON c.dt = a.upperdt 
                 AND b.a = a.a

If possible, I'd like to avoid a CTE.

Upvotes: 1

Views: 790

Answers (1)

MatBailie
MatBailie

Reputation: 86775

You don't want min and max, you really want first and last.

One way I do that is to use ROW_NUMBER() to tell me the position from the begining or the end. Then use MAX(CASE WHEN pos=1 THEN x ELSE null END) to get the values I want.

SELECT
  a,
  MAX(CASE WHEN pos_from_first = 1 THEN dt  ELSE NULL END)  AS first_date,
  MAX(CASE WHEN pos_from_final = 1 THEN dt  ELSE NULL END)  AS final_date,
  MAX(CASE WHEN pos_from_first = 1 THEN val ELSE NULL END)  AS first_value,
  MAX(CASE WHEN pos_from_final = 1 THEN val ELSE NULL END)  AS final_value,
  100
  *
  CAST(MAX(CASE WHEN pos_from_final = 1 THEN val ELSE NULL END) AS DECIMAL(9,6))
  /
  CAST(MAX(CASE WHEN pos_from_first = 1 THEN val ELSE NULL END) AS DECIMAL(9,6))
  -
  100                                                       AS perc_change
FROM
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY a ORDER BY dt  ASC) AS pos_from_first,
    ROW_NUMBER() OVER (PARTITION BY a ORDER BY dt DESC) AS pos_from_final,
    *
  FROM
    tt
)
  AS ordered
GROUP BY
  a

http://sqlfiddle.com/#!6/ad95d/11

Upvotes: 2

Related Questions