2

I have this table (example) :

http://www.sqlfiddle.com/#!9/412b11/1

and i need to insert the average of the whole year + the last year december in the AnualAmount column based on the year, this is the result that i have to obtain using the example data:

Example Result

So in all the Periods of 2018 i have to insert in AnualAmount the AVG of the whole year + the last year december Amount and so on for the other coins and years.

xtrios
  • 125
  • 5
  • 11
  • By now i just got the idea of insert in a table all the decembers in a temp 'table with: SELECT * FROM demo WHERE (RIGHT(Period,2)) = '12' ' and then make a left join to the example table and then try to work with that – xtrios Apr 03 '18 at 04:45
  • 3
    Which database are you using? By the way, I can argue _against_ storing the annual amount in the table, since it is a quantity derived from other data in that table. What happens if you have to amend an amount value somewhere? Then you'd have to do another update. – Tim Biegeleisen Apr 03 '18 at 04:48
  • 1
    Annual Amount how you are calculating is still not clear because when I tried calculating the annual amount I got different from the given value. – Nishant Gupta Apr 03 '18 at 04:57
  • 1
    @TimBiegeleisen I concur. Definite introduction of update anomalies. Also, what is the data type for you `Period` attribute? I just ask because `WHERE(RIGHT(Period,2))='12'` example is a bit awkward. If it's treated as a date or simply a number, a simple `WHERE` range would be easier. (i.e. 201712<=Period<=201812) – Kang Apr 03 '18 at 05:00
  • @Kang it's treated as a varchar but i could change it to date – xtrios Apr 03 '18 at 05:15
  • @TImBiegelsein it's SQL server , and how would it be the best way of make it? – xtrios Apr 03 '18 at 05:18
  • @xtrios Either your data is wrong( as provided in the image) or your logic is incorrect! – DhruvJoshi Apr 03 '18 at 06:27

2 Answers2

1

Kind of clunky but I think it works

declare @T table (Period int, Coin varchar(3), Amount int, AnualAmount varchar(4));
INSERT INTO @T (Period, Coin, Amount, AnualAmount) 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);
declare @D table (Period int);
INSERT INTO @D (Period) values
    (201712),
    (201801),
    (201802),
    (201803),
    (201804),
    (201805),
    (201806),
    (201807),
    (201808),
    (201809),
    (201810),
    (201811),
    (201812);
select t.Coin, t.Period, t.Amount 
     , AVG(t.Amount) over (partition by t.Coin) as Annual     
from @T t
join @D d
on t.Period = d.Period 
union all 
select t.Coin, t.Period, t.Amount  
     , null    
from @T t
left join @D d
on t.Period = d.Period 
where d.Period is null
order by Coin, Period;

Coin Period      Amount      Annual
---- ----------- ----------- -----------
COP  201701      37          NULL
COP  201702      38          NULL
COP  201703      39          NULL
COP  201704      40          NULL
COP  201705      41          NULL
COP  201706      42          NULL
COP  201707      43          NULL
COP  201708      44          NULL
COP  201709      45          NULL
COP  201710      46          NULL
COP  201711      47          NULL
COP  201712      48          54
COP  201801      49          54
COP  201802      50          54
COP  201803      51          54
COP  201804      52          54
COP  201805      53          54
COP  201806      54          54
COP  201807      55          54
COP  201808      56          54
COP  201809      57          54
COP  201810      58          54
COP  201811      59          54
COP  201812      60          54
MXP  201701      13          NULL
MXP  201702      14          NULL
MXP  201703      15          NULL
MXP  201704      16          NULL
MXP  201705      17          NULL
MXP  201706      18          NULL
MXP  201707      19          NULL
MXP  201708      20          NULL
MXP  201709      21          NULL
MXP  201710      22          NULL
MXP  201711      23          NULL
MXP  201712      24          30
MXP  201801      25          30
MXP  201802      26          30
MXP  201803      27          30
MXP  201804      28          30
MXP  201805      29          30
MXP  201806      30          30
MXP  201807      31          30
MXP  201808      32          30
MXP  201809      33          30
MXP  201810      34          30
MXP  201811      35          30
MXP  201812      36          30
REA  201701      61          NULL
REA  201702      62          NULL
REA  201703      63          NULL
REA  201704      64          NULL
REA  201705      65          NULL
REA  201706      66          NULL
REA  201707      67          NULL
REA  201708      68          NULL
REA  201709      69          NULL
REA  201710      70          NULL
REA  201711      71          NULL
REA  201712      72          78
REA  201801      73          78
REA  201802      74          78
REA  201803      75          78
REA  201804      76          78
REA  201805      77          78
REA  201806      78          78
REA  201807      79          78
REA  201808      80          78
REA  201809      81          78
REA  201810      82          78
REA  201811      83          78
REA  201812      84          78
paparazzo
  • 44,497
  • 23
  • 105
  • 176
0

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.

EzLo
  • 13,780
  • 10
  • 33
  • 38