You can try the following update. In here we force the previous month to be included in the next year, so there might be periods (by coin) that include up to 13 months. Please note that I changed Amount
and AnnualAmount
data type to DECIMAL
(since you are using decimals and not just integers). Also, the December months are used for 2 different averages (current and next year), in this query we are updating it's AnnualAmount of the current year.
IF OBJECT_ID('tempdb..#Table') IS NOT NULL
DROP TABLE #Table
CREATE TABLE #Table (
Period int,
Coin varchar(3),
Amount DECIMAL(8,2),
AnnualAmount DECIMAL(8,2))
INSERT INTO #Table (Period, Coin, Amount, AnnualAmount)
VALUES
(201701, 'MXP', 13.5, NULL),
(201702, 'MXP', 14.5, NULL),
(201703, 'MXP', 15.5, NULL),
(201704, 'MXP', 16.5, NULL),
(201705, 'MXP', 17.5, NULL),
(201706, 'MXP', 18.5, NULL),
(201707, 'MXP', 19.5, NULL),
(201708, 'MXP', 20.5, NULL),
(201709, 'MXP', 21.5, NULL),
(201710, 'MXP', 22.5, NULL),
(201711, 'MXP', 23.5, NULL),
(201712, 'MXP', 24.5, NULL),
(201801, 'MXP', 25.5, NULL),
(201802, 'MXP', 26.5, NULL),
(201803, 'MXP', 27.5, NULL),
(201804, 'MXP', 28.5, NULL),
(201805, 'MXP', 29.5, NULL),
(201806, 'MXP', 30.5, NULL),
(201807, 'MXP', 31.5, NULL),
(201808, 'MXP', 32.5, NULL),
(201809, 'MXP', 33.5, NULL),
(201810, 'MXP', 34.5, NULL),
(201811, 'MXP', 35.5, NULL),
(201812, 'MXP', 36.5, NULL),
(201701, 'COP', 37.5, NULL),
(201702, 'COP', 38.5, NULL),
(201703, 'COP', 39.5, NULL),
(201704, 'COP', 40.5, NULL),
(201705, 'COP', 41.5, NULL),
(201706, 'COP', 42.5, NULL),
(201707, 'COP', 43.5, NULL),
(201708, 'COP', 44.5, NULL),
(201709, 'COP', 45.5, NULL),
(201710, 'COP', 46.5, NULL),
(201711, 'COP', 47.5, NULL),
(201712, 'COP', 48.5, NULL),
(201801, 'COP', 49.5, NULL),
(201802, 'COP', 50.5, NULL),
(201803, 'COP', 51.5, NULL),
(201804, 'COP', 52.5, NULL),
(201805, 'COP', 53.5, NULL),
(201806, 'COP', 54.5, NULL),
(201807, 'COP', 55.5, NULL),
(201808, 'COP', 56.5, NULL),
(201809, 'COP', 57.5, NULL),
(201810, 'COP', 58.5, NULL),
(201811, 'COP', 59.5, NULL),
(201812, 'COP', 60.5, NULL),
(201701, 'REA', 61.5, NULL),
(201702, 'REA', 62.5, NULL),
(201703, 'REA', 63.5, NULL),
(201704, 'REA', 64.5, NULL),
(201705, 'REA', 65.5, NULL),
(201706, 'REA', 66.5, NULL),
(201707, 'REA', 67.5, NULL),
(201708, 'REA', 68.5, NULL),
(201709, 'REA', 69.5, NULL),
(201710, 'REA', 70.5, NULL),
(201711, 'REA', 71.5, NULL),
(201712, 'REA', 72.5, NULL),
(201801, 'REA', 73.5, NULL),
(201802, 'REA', 74.5, NULL),
(201803, 'REA', 75.5, NULL),
(201804, 'REA', 76.5, NULL),
(201805, 'REA', 77.5, NULL),
(201806, 'REA', 78.5, NULL),
(201807, 'REA', 79.5, NULL),
(201808, 'REA', 80.5, NULL),
(201809, 'REA', 81.5, NULL),
(201810, 'REA', 82.5, NULL),
(201811, 'REA', 83.5, NULL),
(201812, 'REA', 84.5, NULL);
;WITH ParsedDates AS
(
SELECT
T.*,
Year = T.Period / 100,
Month = T.Period % 100
FROM
#Table AS T
),
CoinPeriods AS
(
SELECT
P.Amount,
P.Coin,
P.YEAR,
P.Month
FROM
ParsedDates AS P
UNION ALL
SELECT
N.Amount,
N.Coin,
Year = N.YEAR + 1, -- Force previous year's months to be included in next year calculation
N.Month
FROM
ParsedDates AS N
WHERE
N.Month = 12
),
AnnualAmounts AS
(
SELECT
P.Coin,
P.Year,
AnnualAmount = AVG(P.Amount)
FROM
CoinPeriods AS P
GROUP BY
P.Coin,
P.Year
)
UPDATE T SET
AnnualAmount = A.AnnualAmount
FROM
#Table AS T
INNER JOIN AnnualAmounts AS A ON
T.Coin = A.Coin AND
T.Period / 100 = A.Year
/*
SELECT * FROM #Table AS T
Results:
Period Coin Amount AnnualAmount
201701 MXP 13.50 19.00
201702 MXP 14.50 19.00
201703 MXP 15.50 19.00
201704 MXP 16.50 19.00
201705 MXP 17.50 19.00
201706 MXP 18.50 19.00
201707 MXP 19.50 19.00
201708 MXP 20.50 19.00
201709 MXP 21.50 19.00
201710 MXP 22.50 19.00
201711 MXP 23.50 19.00
201712 MXP 24.50 19.00
201801 MXP 25.50 30.50
201802 MXP 26.50 30.50
201803 MXP 27.50 30.50
201804 MXP 28.50 30.50
201805 MXP 29.50 30.50
201806 MXP 30.50 30.50
201807 MXP 31.50 30.50
201808 MXP 32.50 30.50
201809 MXP 33.50 30.50
201810 MXP 34.50 30.50
201811 MXP 35.50 30.50
201812 MXP 36.50 30.50
201701 COP 37.50 43.00
201702 COP 38.50 43.00
201703 COP 39.50 43.00
201704 COP 40.50 43.00
201705 COP 41.50 43.00
201706 COP 42.50 43.00
201707 COP 43.50 43.00
201708 COP 44.50 43.00
201709 COP 45.50 43.00
201710 COP 46.50 43.00
201711 COP 47.50 43.00
201712 COP 48.50 43.00
201801 COP 49.50 54.50
201802 COP 50.50 54.50
201803 COP 51.50 54.50
201804 COP 52.50 54.50
201805 COP 53.50 54.50
201806 COP 54.50 54.50
201807 COP 55.50 54.50
201808 COP 56.50 54.50
201809 COP 57.50 54.50
201810 COP 58.50 54.50
201811 COP 59.50 54.50
201812 COP 60.50 54.50
201701 REA 61.50 67.00
201702 REA 62.50 67.00
201703 REA 63.50 67.00
201704 REA 64.50 67.00
201705 REA 65.50 67.00
201706 REA 66.50 67.00
201707 REA 67.50 67.00
201708 REA 68.50 67.00
201709 REA 69.50 67.00
201710 REA 70.50 67.00
201711 REA 71.50 67.00
201712 REA 72.50 67.00
201801 REA 73.50 78.50
201802 REA 74.50 78.50
201803 REA 75.50 78.50
201804 REA 76.50 78.50
201805 REA 77.50 78.50
201806 REA 78.50 78.50
201807 REA 79.50 78.50
201808 REA 80.50 78.50
201809 REA 81.50 78.50
201810 REA 82.50 78.50
201811 REA 83.50 78.50
201812 REA 84.50 78.50
*/
I agree with Tim in that storing a calculated column that depends on more than 1 row is a sign for a bad design (updating the Amount
of one row will require to recalculate the AnnualAmount
of several ones). In this case, it would be best to keep this value in an aggregate separate table (by year and coin) or just calculate it to be displayed and not actually stored.