CODEWITHSUNDEEP
sql-server
ninja112
ninja112

Reputation: 23

Find difference between two products based on date

I currently have a table (prediction) with the first column containing two types of data, actual and a forecast. I want to find the difference based on the month between the two forecasts. The table looks like this:

Data     | Product |    Forecast M1 | Forecast M2 | Forecast M3
Actual        A              13          16             18
Actual        B              22          24             30
Forecast      A              15          14             21
Forecast      B              24          25             26

I want to query a table that compares the two products based on the month to show the absolute value of the difference between the actual and the forecast to look like this:

Product |    M1 difference | M2 difference | M3 difference
A              2                  2              3
B              2                  1              4

Is this a simple query or does a new table need to be created?

Upvotes: 0

Views: 37

Answers (2)

Dave.Gugg
Dave.Gugg

Reputation: 6781

You can use a query like this:

SELECT act.Product
     , act.M1 - forc.M1 AS M1Diff
     , act.M2 - forc.M2 AS M2Diff
     , act.M3 - forc.M3 AS M3Diff
FROM #notnormal AS act
    INNER JOIN #notnormal AS forc ON forc.Product = act.Product
                                     AND act.Data = 'Actual'
                                     AND forc.Data = 'Forecast';

Here's the schema I set up to show it:

CREATE TABLE #notnormal
(
    Data VARCHAR(10)
  , Product CHAR(1)
  , M1 SMALLINT
  , M2 SMALLINT
  , M3 SMALLINT
);
INSERT INTO #notnormal
(
    Data
  , Product
  , M1
  , M2
  , M3
)
VALUES
('Actual', 'A', 13, 16, 18)
, ('Actual', 'B', 22, 24, 30)
, ('Forecast', 'A', 15, 14, 21)
, ('Forecast', 'B', 24, 25, 26);

Upvotes: 0

sgeddes
sgeddes

Reputation: 62851

Here's one option using a self join and abs:

select t1.product,
       abs(t1.forecastm1 - t2.forecastm1) as m1difference,
       abs(t1.forecastm2 - t2.forecastm2) as m2difference,
       abs(t1.forecastm3 - t2.forecastm3) as m3difference
from prediction t1
    join prediction t2 on t1.product = t2.product
where t1.data = 'Actual' 
    and t2.data = 'Forecast'

Upvotes: 1

Related Questions