Using Pivot to calculate% change over the last two days

advertisements

I have a table that contains a set of data that is being collected on a daily basis (I use SQL Server 2016)

Date        A     B     C    D
2017/5/22   1.1   2.1  3.1   4.1
2017/5/21   1.0   2.0  3.0   4.0
2017/5/20   0.9   1.9  2.9   3.9
2017/5/19   1.0   2.0  3.0   4.0
2017/5/18   1.1   2.1  3.1   4.1

I'm trying to write a query that will show me the last two days and the % change, returning the results as follows:

Field  Today   Yesterday  Change(%)
A       1.1       1.0       10.0%
B       2.1       2.0        5.0%
C       3.1       3.0        3.3%
D       4.1       4.0        2.5%

Is there a quick way to do this (I'm assuming pivot is involved somewhere but I really can't work this query out)


I'd get the result by first using a windowing function, like row_number to get the top 2 dates, then unpivot your columns of A, B, C, and D into rows. Once you've done that, you can pivot those results to get the final desired product.

Breaking this down, I'd start by using row_number:

select [Date], A, B, C, D,
  rn = row_number() over(order by [Date] desc)
from #yourtable

This creates a unique row id for each row in your table, you can order this by date to generate the dates in the order you'd like. Next up, you'll unpivot your A, B, C, and D columns into rows:

select
  Field,
  value,
  Dt = case when rn = 1 then 'Today' else 'Yesterday' end
from
(
  select [Date], A, B, C, D,
    rn = row_number() over(order by [Date] desc)
  from #yourtable
) x
cross apply
(
  values
  ('A', A),
  ('B', B),
  ('C', C),
  ('D', D)  -- include additional columns here if you have more
) c (Field, value)
where rn <= 2 -- return top 2 dates

In this you'll convert your columns into rows and then only return those with the top 2 dates you want - today and yesterday. Finally, you'll pivot those Today and Yesterday values into columns and calculate your percent change. So putting it all together:

select
  Field,
  Today,
  Yesterday,
  ChangePercent = round((Today-Yesterday)/ Yesterday *100.0, 2)
from
(
  select
    Field,
    value,
    Dt = case when rn = 1 then 'Today' else 'Yesterday' end
  from
  (
    select [Date], A, B, C, D,
      rn = row_number() over(order by [Date] desc)
    from #yourtable
  ) x
  cross apply
  (
    values
    ('A', A),
    ('B', B),
    ('C', C),
    ('D', D)
  ) c (Field, value)
  where rn <= 2 -- return top 2 dates
) d
pivot
(
  max(value)
  for dt in (Today, Yesterday)
) piv

Here is a demo. This gives you the results:

Field Today Yesterday ChangePercent
----- ----- --------- -------------
A     1.1   1         10
B     2.1   2         5
C     3.1   3         3.33
D     4.1   4         2.5