Need help on calculation query

  • Hi,

    I have a summary table which store all kinds of business measurements (there's 86 measure_name, I list only 2 for example usage) and values. Example:

    Location2   Measure_Name    Location1    Measure_Value

    S00152      TY_Sales            Chicago       2111.05

    S00152      PY_Sales            Chicago       2200.65

    D1005       TY_Sales             Chicago      12240.05

    D1005       PY_Sales             Chicago      10079.87

    D2188       TY_Sales             Chicago      11555.21

    D2188       PY_Sales             Chicago      10800.44

    Chicago     TY_Sales            All              132240.05

    Chicago     PY_Sales            All              132079.87

    We need to calculate Comp Sales and the formula is "(TY_Sales-PY_Sales)/PY_Sales"

    The result set should be

    Location2   Location1    Comp_Sales

    S00152      Chicago      -0.041

    D1005        Chicago       0.214

    D2188       Chicago        0.070

    Chicago     All               0.001

    How to do that?

  • Table design discussions set aside, this should get you waht you want:

    select

    s1

    .Location2,

    s1

    .Location1,

    (s1.Measure_Value - s2.Measure_Value)/s2.Measure_Value as Comp_Sales

    from

    YourTable s1

    INNER

    JOIN

    YourTable s2

    ON

    s1

    .Location1 = s2.Location1

    AND

    s1

    .Location2 = s2.Location2

    WHERE

    s1

    .Measure_Name = 'TY_Sales'

    AND

    s2

    .Measure_Name = 'PY_Sales'

     

  • -- Prepare test data

    declare @test table (Location2 varchar(7), Measure_Name varchar(8), Location1 varchar(7), Measure_Value smallmoney)

    insert @test

    select 'S00152', 'TY_Sales', 'Chicago', 2111.05 union all

    select 'S00152', 'PY_Sales', 'Chicago', 2200.65 union all

    select 'D1005', 'TY_Sales', 'Chicago', 12240.05 union all

    select 'D1005', 'PY_Sales', 'Chicago', 10079.87 union all

    select 'D2188', 'TY_Sales', 'Chicago', 11555.21 union all

    select 'D2188', 'PY_Sales', 'Chicago', 10800.44 union all

    select 'Chicago', 'TY_Sales', 'All', 132240.05 union all

    select 'Chicago', 'PY_Sales', 'All', 132079.87

    -- Do the work

    SELECT  z.Location2,

            z.Location1,

            CASE WHEN z.PY = 0 THEN NULL ELSE (z.TY - z.PY) / z.PY END Perc

    FROM    (

                SELECT   Location1,

                         Location2,

                         SUM(CASE WHEN Measure_Name = 'TY_Sales' THEN Measure_Value ELSE 0 END) TY,

                         SUM(CASE WHEN Measure_Name = 'PY_Sales' THEN Measure_Value ELSE 0 END) PY

                FROM     @test-2

                GROUP BY Location1,

                         Location2

            ) z


    N 56°04'39.16"
    E 12°55'05.25"

  • Thank you so much.  Both queries work perfectly.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply