sqlexceloracle-database

Translate excel formulas into SQL query

I am looking for help to translate excel formulas into SQL (Oracle). With given fixed input values for rows 1 (Month ascending) and 2 (SU - some number). I have to calculate through particular cells to get value for D8.

enter image description here

Complexity is, that formulas reference values in recurring levels/previous column/earlier months

Let me provide calculations for one column, as an example let's take column D

D1 - Month
D2 - SU 
D3 - C7 
D4 - C4 + B7
D5 - D3 * 1.54 + D4
D6 - D2 - D5
D7 - D6 / 2.38
D8 - D3 + D4 + D7

Below queries to build and populate exemplary table and populate it with data.

  CREATE TABLE TESTOSS
   (    PERIOD_MONTH VARCHAR2(20 CHAR), 
    SU NUMBER
   );
   
Insert into TESTOSS (PERIOD_MONTH,SU) values ('2020-09',503);
Insert into TESTOSS (PERIOD_MONTH,SU) values ('2020-10',1109);
Insert into TESTOSS (PERIOD_MONTH,SU) values ('2020-11',1537);
Insert into TESTOSS (PERIOD_MONTH,SU) values ('2020-12',2327);
Insert into TESTOSS (PERIOD_MONTH,SU) values ('2021-01',3000);
Insert into TESTOSS (PERIOD_MONTH,SU) values ('2021-02',4000);
Insert into TESTOSS (PERIOD_MONTH,SU) values ('2021-03',5000);
Insert into TESTOSS (PERIOD_MONTH,SU) values ('2021-04',3292);
Insert into TESTOSS (PERIOD_MONTH,SU) values ('2021-05',3721);
Insert into TESTOSS (PERIOD_MONTH,SU) values ('2021-06',4149);
Insert into TESTOSS (PERIOD_MONTH,SU) values ('2021-07',4578);
Insert into TESTOSS (PERIOD_MONTH,SU) values ('2021-08',5006);
Insert into TESTOSS (PERIOD_MONTH,SU) values ('2021-09',5435);
Insert into TESTOSS (PERIOD_MONTH,SU) values ('2021-10',5863);
Insert into TESTOSS (PERIOD_MONTH,SU) values ('2021-11',6292);
Insert into TESTOSS (PERIOD_MONTH,SU) values ('2021-12',6720);
Insert into TESTOSS (PERIOD_MONTH,SU) values ('2022-02',7577);
Insert into TESTOSS (PERIOD_MONTH,SU) values ('2022-01',7149);

Does anybody got any clue how to tackle this one?

Solution

  • You can use a MODEL clause:

    SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (ORDER BY period_month) AS rn
  FROM TESTOSS t
)
MODEL
  DIMENSION BY (rn)
  MEASURES (
    period_month, SU, 0 AS t1, 0 AS t2, 0 AS rp_su, 0 AS n_su, 0 AS np, 0 AS tpe
  )
  RULES AUTOMATIC ORDER (
    t1[rn>1]  = np[cv(rn)-1],
    t2[rn>2]  = t2[cv(rn)-1] + np[cv(rn)-2],
    rp_su[rn] = t1[cv(rn)] * 1.54 + t2[cv(rn)],
    n_su[rn]  = su[cv(rn)] - rp_su[cv(rn)],
    np[rn]    = n_su[cv(rn)] / 2.38,
    tpe[rn]   = t1[cv(rn)] + t2[cv(rn)] + np[cv(rn)]
  )
order by rn;

    Which, for the sample data, outputs:

    RN PERIOD_MONTH SU T1 T2 RP_SU N_SU NP TPE
    1 2020-09 503 0 0 0 503 211.344537815126050420168067226890756303 211.344537815126050420168067226890756303
    2 2020-10 1109 211.344537815126050420168067226890756303 0 325.470588235294117647058823529411764707 783.529411764705882352941176470588235293 329.214038556599110232328225407810182896 540.558576371725160652496292634700939199
    3 2020-11 1537 329.214038556599110232328225407810182896 211.344537815126050420168067226890756303 718.334157192288680177953534354918437963 818.665842807711319822046465645081562037 343.977244877189630177330447750034269763 884.535821248914790829826740384735208962
    4 2020-12 2327 343.977244877189630177330447750034269763 540.558576371725160652496292634700939199 1070.283533482597191125585182169753714634 1256.716466517402808874414817830246285366 528.032128788824709611098662953884993851 1412.567950037739500440925403338620202813
    5 2021-01 3000 528.032128788824709611098662953884993851 884.535821248914790829826740384735208962 1697.705299583704843630918681333718099493 1302.294700416295156369081318666281900507 547.182647233737460659277864985832731305 1959.750597271476961100203268324452934118
    6 2021-02 4000 547.182647233737460659277864985832731305 1412.567950037739500440925403338620202813 2255.229226777695189856213315416802609023 1744.770773222304810143786684583197390977 733.096963538783533673859951505545122259 2692.847560810260494774063219829998056377
    7 2021-03 5000 733.096963538783533673859951505545122259 1959.750597271476961100203268324452934118 3088.719921121203602957947593642992422397 1911.280078878796397042052406357007577603 803.058856671763192034475800990339318321 3495.906417482023686808539020820337374698
    8 2021-04 3292 803.058856671763192034475800990339318321 2692.847560810260494774063219829998056377 3929.558200084775810507155953355120606591 -637.558200084775810507155953355120606591 -267.881596674275550633258803930722943946 3228.024820807748136175280216889614430752
    9 2021-05 3721 -267.881596674275550633258803930722943946 3495.906417482023686808539020820337374698 3083.368758603639338833320462767024041021 637.631241396360661166679537232975958979 267.912286300991874439781318165115949151 3495.937107108740010615061535054730379903
    10 2021-06 4149 267.912286300991874439781318165115949151 3228.024820807748136175280216889614430752 3640.609741711275622812543446863892992445 508.390258288724377187456553136107007555 213.609352222153099658595190393322272082 3709.546459330893110273656725448052651985
    11 2021-07 4578 213.609352222153099658595190393322272082 3495.937107108740010615061535054730379903 3824.895509530855784089298128260446678909 753.104490469144215910701871739553321091 316.430458180312695760799105772921563484 4025.976917511205806034455831220974215469
    12 2021-08 5006 316.430458180312695760799105772921563484 3709.546459330893110273656725448052651985 4196.84936492857466174528734833835185975 809.15063507142533825471265166164814025 339.979258433371990863324643555314344643 4365.956175944577796897780474776288560112
    13 2021-09 5435 339.979258433371990863324643555314344643 4025.976917511205806034455831220974215469 4549.544975498598671963975782296158306219 885.455024501401328036024217703841693781 372.03992626109299497311941920329482932 4737.996102205670791870899893979583389432
    14 2021-10 5863 372.03992626109299497311941920329482932 4365.956175944577796897780474776288560112 4938.897662386661009156384380349362597265 924.102337613338990843615619650637402735 388.278293114848315480510764559091345687 5126.274395320519107351410658538674735119
    15 2021-11 6292 388.278293114848315480510764559091345687 4737.996102205670791870899893979583389432 5335.94467360253719771088647140058406179 956.05532639746280228911352859941593821 401.70391865439613541559391957958652866 5527.978313974915242767004578118261263779
    16 2021-12 6720 401.70391865439613541559391957958652866 5126.274395320519107351410658538674735119 5744.898430048289155891425294691237989255 975.101569951710844108574705308762010745 409.706541996517161390157439205362189389 5937.684855971432404157162017323623453168
    17 2022-01 7149 409.706541996517161390157439205362189389 5527.978313974915242767004578118261263779 6158.926388649551671307847034494519035438 990.073611350448328692152965505480964562 415.99731569346568432443401911994998511 6353.682171664898088481596036443573438278
    18 2022-02 7577 415.99731569346568432443401911994998511 5937.684855971432404157162017323623453168 6578.320722139369558016790406768346430237 998.679277860630441983209593231653569763 419.61314195824808486689478707212334864 6773.295313623146173348490823515696786918

    db<>fiddle here