CODEWITHSUNDEEP
sqlsql-serverpivot
Richard B
Richard B

Reputation: 935

Using Pivot to Calculate % data change in last two days

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)

Upvotes: 0

Views: 131

Answers (2)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

This should give you what you need:

select
    FieldValue                          as  [Field]
    , case FieldValue 
        when 'A'
            then ta
        when 'B'
            then tb
        when 'C'
            then tc
        when 'D'
            then td
        end                             as  [Today]
    , case FieldValue 
        when 'A'
            then ya
        when 'B'
            then yb
        when 'C'
            then yc
        when 'D'
            then yd
        end                             as  [Yesterday]
    , Change                            as  [Change(%)]
    from
    (select
        t1.a                            as  [ta]    -- today's A value
        , t1.b                          as  [tb]    -- today's B value
        , t1.c                          as  [tc]    -- today's C value
        , t1.d                          as  [td]    -- today's D value
        --, t1.e                        as  [te]    -- today's E value
        -- make sure to include the t1.e, t1.f etc. for other Fields too

        , y.a                           as  [ya]    -- yesterday's A value
        , y.b                           as  [yb]    -- yesterday's B value
        , y.c                           as  [yc]    -- yesterday's C value
        , y.d                           as  [yd]    -- yesterday's D value
        --, y.e                         as  [ye]    -- yesterday's E value
        -- make sure to include the y.e, y.f etc. for other Fields too

        , 100 / (y.a / (t1.a - y.a))    as  [A]     -- A's change since yesterday
        , 100 / (y.b / (t1.b - y.b))    as  [B]     -- B's change since yesterday
        , 100 / (y.c / (t1.c - y.c))    as  [C]     -- C's change since yesterday
        , 100 / (y.d / (t1.d - y.d))    as  [D]     -- D's change since yesterday
        --, 100 / (y.e / t1.e - y.e))   as  [E]     -- E's change since yesterday (INCLUDE this "E" alias in the list of columns from UNPIVOT)
        -- make sure to add calculations for your other fields here too

    from baseTable t1
        cross apply (select top 1 * 
                     from baseTable t2 
                     where t2.date < t1.date) y
    where t1.date = (select max(date) from baseTable)
    ) result
unpivot (
    Change for FieldValue in (a, b, c, d) --, e, f etc.) -- enumerate all column ALIASES used in the sub-select, where the CHANGE is calculated
    ) as unpvt

Just make sure to extend the CASE statements and UNPIVOT columns with all your values as well as the 100 / (x.a / (t1.a - x.a)) with all of your other fields.

Script I used to generate sample data: (ideally you should have provided this)

CREATE TABLE [dbo].[baseTable](
    [date] [date] NULL,
    [a] [numeric](18, 1) NULL,
    [b] [numeric](18, 1) NULL,
    [c] [numeric](18, 1) NULL,
    [d] [numeric](18, 1) NULL
)

GO
INSERT [dbo].[baseTable] ([date], [a], [b], [c], [d]) VALUES (CAST(N'2017-05-22' AS Date), CAST(1.1 AS Numeric(18, 1)), CAST(2.1 AS Numeric(18, 1)), CAST(3.1 AS Numeric(18, 1)), CAST(4.1 AS Numeric(18, 1)))
GO
INSERT [dbo].[baseTable] ([date], [a], [b], [c], [d]) VALUES (CAST(N'2017-05-21' AS Date), CAST(1.0 AS Numeric(18, 1)), CAST(2.0 AS Numeric(18, 1)), CAST(3.0 AS Numeric(18, 1)), CAST(4.0 AS Numeric(18, 1)))
GO
INSERT [dbo].[baseTable] ([date], [a], [b], [c], [d]) VALUES (CAST(N'2017-05-20' AS Date), CAST(0.9 AS Numeric(18, 1)), CAST(1.9 AS Numeric(18, 1)), CAST(2.9 AS Numeric(18, 1)), CAST(3.9 AS Numeric(18, 1)))
GO
INSERT [dbo].[baseTable] ([date], [a], [b], [c], [d]) VALUES (CAST(N'2017-05-19' AS Date), CAST(1.0 AS Numeric(18, 1)), CAST(2.0 AS Numeric(18, 1)), CAST(3.0 AS Numeric(18, 1)), CAST(4.0 AS Numeric(18, 1)))
GO
INSERT [dbo].[baseTable] ([date], [a], [b], [c], [d]) VALUES (CAST(N'2017-05-18' AS Date), CAST(1.1 AS Numeric(18, 1)), CAST(2.1 AS Numeric(18, 1)), CAST(3.1 AS Numeric(18, 1)), CAST(4.1 AS Numeric(18, 1)))
GO

Upvotes: 0

ollie
ollie

Reputation: 1009

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

Upvotes: 2

Related Questions