locked
How to calculate % of increase between days RRS feed

  • Question

  • Hi, I am not sure how to write a query to calculate % of increase of different values between various days. To explain it better, I have a table with 3 columns, Date, CompanyName, Value. I want to calculate % difference for each company between several days.
    Date       Company  Value
    2010-03-01  Ford    10.2
    2010-03-02  Ford    10.4
    2010-03-03  Ford    11.1
    2010-03-04  Ford    10.8
    2010-03-05  Ford    11.5
    2010-03-01  Google  10.2
    2010-03-02  Google  10.4
    2010-03-03  Google  11.1
    2010-03-04  Google  10.8
    2010-03-05  Google  11.5
    The results I want to show in two ways:

    Date | Company | Value | % increase

    Company | Date1 | Date2 | Date3 | Date4

    How to do it?

    Wednesday, March 17, 2010 7:35 PM

Answers

  • To show the %Increase, you can do it like so (SQL2005 or beyond only):

    This is just off the top of my head... it's untested:

    ;WITH RowNumsAdded AS
    (
      SELECT [Date],Company,Value
                 ,RowNum=ROW_NUMBER() OVER (PARTITION BY Company ORDER BY [Date])
      FROM MyTable
    )
    SELECT Curr.[Date],Curr.Company,Curr.Value
               ,PercentIncrease=100.0*(Curr.Value-Prev.Value)/Prev.Value
    FROM RowNumsAdded Curr
    LEFT JOIN RowNumsAdded Prev ON Curr.Company=Prev.Company AND Curr.RowNum-1=Prev.RowNum

    --Brad (My Blog)
    • Proposed as answer by Adam Haines Wednesday, March 17, 2010 7:51 PM
    • Marked as answer by kapiszon2929 Thursday, March 18, 2010 8:57 AM
    Wednesday, March 17, 2010 7:43 PM

All replies

  • 1. You can fisrt Pivot the data to show value in Date1, Date2, Date3, Date4 and then calculate the % increase.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, March 17, 2010 7:43 PM
  • To show the %Increase, you can do it like so (SQL2005 or beyond only):

    This is just off the top of my head... it's untested:

    ;WITH RowNumsAdded AS
    (
      SELECT [Date],Company,Value
                 ,RowNum=ROW_NUMBER() OVER (PARTITION BY Company ORDER BY [Date])
      FROM MyTable
    )
    SELECT Curr.[Date],Curr.Company,Curr.Value
               ,PercentIncrease=100.0*(Curr.Value-Prev.Value)/Prev.Value
    FROM RowNumsAdded Curr
    LEFT JOIN RowNumsAdded Prev ON Curr.Company=Prev.Company AND Curr.RowNum-1=Prev.RowNum

    --Brad (My Blog)
    • Proposed as answer by Adam Haines Wednesday, March 17, 2010 7:51 PM
    • Marked as answer by kapiszon2929 Thursday, March 18, 2010 8:57 AM
    Wednesday, March 17, 2010 7:43 PM
  • Brad,

    It would not show the increase as Date1, Date2, Date3, Date4 fields.

    We first need to pivot the data and then do Date2-Date1/Date1 * 100%,

    Date3-Date2/Date2 * 100, etc. to get the data in desired format using T-SQL.

    Otherwise sounds to me better suited for SSAS.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, March 17, 2010 8:04 PM
  • Brad,

    It would not show the increase as Date1, Date2, Date3, Date4 fields.

    We first need to pivot the data and then do Date2-Date1/Date1 * 100%,

    Date3-Date2/Date2 * 100, etc. to get the data in desired format using T-SQL.

    Otherwise sounds to me better suited for SSAS.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    It looks like he asked for two different types of output.  I just addressed the first one... I didn't address the pivoted part.

    It would be something like:

    ;WITH RowNumsAdded AS
    (
      SELECT [Date],Company
                 ,ColName='Date'+LTRIM(STR(ROW_NUMBER() OVER (PARTITION BY Company ORDER BY [Date])))
      FROM MyTable
    )
    SELECT Company,Date1,Date2,Date3,Date4
    FROM RowNumsAdded
    PIVOT (MAX([Date]) FOR ColName IN (Date1,Date2,Date3,Date4)) P

    That's assuming he wants the dates in the pivot columns... if he wants values, then we would do kind of a combo of the first query I posted and the one above.


    --Brad (My Blog)
    Wednesday, March 17, 2010 8:11 PM
  • Brad, great thanks, your code works well, this is what I wanted. The only thing is that I need to format the PercentIncrease column as the number looks like this: 1,40255905511811. I tried to apply Round and Cast but got a message "Arithmetic overflow error converting float to data type numeric." . Probably because there are some Nulls in this column.

    I eliminated all Nulls by using CASE WHEN ... but still the CAST doesn't work, it gives me the same error. How can that be?

    ;WITH RowNumsAdded AS
    (
      SELECT Fecha,Company,Cotizacion,RowNum=ROW_NUMBER() OVER (PARTITION BY Company ORDER BY Fecha)
      FROM dbo.IBEX_Cotizaciones
      WHERE Fecha >= '2010-03-14'
    )
    SELECT Curr.Fecha,Curr.Company,Curr.Cotizacion,PercentIncrease=CASE WHEN 100.0*(Curr.Cotizacion-Prev.Cotizacion)/Prev.Cotizacion IS NULL THEN NULL ELSE 
    		CAST(100*(Curr.Cotizacion-Prev.Cotizacion)/Prev.Cotizacion AS decimal(4,4)) END
    FROM RowNumsAdded Curr
    LEFT JOIN RowNumsAdded Prev ON Curr.Company=Prev.Company AND Curr.RowNum-1=Prev.RowNum
    And regarding the second query, I will have to try it out. I don't know if this Pivot query will work fast.
    Wednesday, March 17, 2010 10:25 PM
  • You shouldn't have a problem with NULLs, since any arithmetic operation involving NULLs just equals NULL.

    Your DECIMAL(4,4) is not big enough.  You have to increase the left-hand number.

    Try CASTing it to DECIMAL(7,4) instead.


    --Brad (My Blog)
    Wednesday, March 17, 2010 10:30 PM
  • Try using ROUND function instead of the casting to decimal.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, March 17, 2010 10:36 PM
  • Try using ROUND function instead of the casting to decimal.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    Even if he rounds, the result will still have several digits to the right of the decimal point.

    I think he wants a maximum of 4 decimal places... thus the CAST.
    --Brad (My Blog)
    Wednesday, March 17, 2010 10:41 PM
  • thanks Brad, you were very helpful, the query now runs perfectly. Excellent!
    Thursday, March 18, 2010 8:57 AM