גרי רשף

Use tempdb;

Go

If Object_Id('T_Toshavim','U') Is Not Null Drop Table T_Toshavim;

Go

Create table T_Toshavim(Shana SmallInt Primary Key,

                        Toshavim Decimal(5,1),

                        Shinuy Decimal(5,1));

Go

Insert

Into T_Toshavim(Shana,Toshavim,Shinuy)

Values  (1948,872.7,NULL),

        (1949,1173.9,301.2),

        (1950,1370.1,196.2),

        (1951,1577.8,207.7),

        (1952,1629.5,51.7),

        (1953,1669.4,39.9),

        (1954,1717.8,48.4),

        (1955,1789.1,71.3),

        (1956,1872.4,83.3),

        (1957,1976.0,103.6),

        (1958,2031.7,55.7),

        (1959,2088.7,57.0),

        (1960,2150.4,61.7),

        (1961,2234.2,83.8),

        (1962,2331.8,97.6),

        (1963,2430.1,98.3),

        (1964,2525.6,95.5),

        (1965,2598.4,72.8),

        (1966,2657.4,59.0),

        (1967,2776.3,118.9),

        (1968,2841.1,64.8),

        (1969,2929.5,88.4),

        (1970,3022.1,92.6),

        (1971,3120.7,98.6),

        (1972,3225.0,104.3),

        (1973,3338.2,113.2),

        (1974,3421.6,83.4),

        (1975,3493.2,71.6),

        (1976,3575.4,82.2),

        (1977,3653.2,77.8),

        (1978,3737.6,84.4),

        (1979,3836.2,98.6),

        (1980,3921.7,85.5),

        (1981,3977.7,56.0),

        (1982,4063.6,85.9),

        (1983,4148.5,84.9),

        (1984,4199.7,51.2),

        (1985,4266.2,66.5),

        (1986,4331.3,65.1),

        (1987,4406.5,75.2),

        (1988,4476.8,70.3),

        (1989,4559.6,82.8),

        (1990,4821.7,262.1),

        (1991,5058.8,237.1),

        (1992,5195.9,137.1),

        (1993,5327.6,131.7),

        (1994,5471.5,143.9),

        (1995,5612.3,140.8),

        (1996,5757.9,145.6),

        (1997,5900.0,142.1),

        (1998,6041.4,141.4),

        (1999,6209.1,167.7),

        (2000,6369.3,160.2),

        (2001,6508.8,139.5),

        (2002,6631.1,122.3),

        (2003,6748.4,117.3),

        (2004,6869.5,121.1),

        (2005,6990.7,121.2),

        (2006,7116.7,126.0),

        (2007,7243.6,126.9),

        (2008,7419.1,175.5),

        (2009,7552.0,132.9),

        (2010,7695.1,143.1);

Go

Select  *

From    T_Toshavim

Order By Shana;

Go

Select  *, 

        Toshavim-Lag(Toshavim,1) Over(Order By Shana) Shinuy1 

From    T_Toshavim 

Order By Shana; 

Select  *, 

        First_Value(Toshavim) Over(Order By Shana)+ 

        Sum(Shinuy) Over(Order By Shana Rows Between Unbounded Preceding And Current Row) Toshavim1 

From T_Toshavim 

Order By Shana; 

If Object_Id('T_Madad','U') Is Not Null Drop Table T_Madad; 

Go 

Create Table T_Madad(Shana SmallInt, 

                    Hodesh TinyInt, 

                    Madad Decimal(10,7), 

                    Shinuy Decimal(19,18), 

                    Constraint PK_T_Madad Primary Key Clustered(Shana, Hodesh)); 

Go 

Insert 

Into    T_Madad 

Values  (2010, 12, 101.7907634, 0.003717471847701109), 

        (2010, 11, 101.4137606, 0.000930232490198810), 

        (2010, 10, 101.3195099,0.002798507257722001 ), 

        (2010, 9, 101.0367578, 0.002806360879009696), 

        (2010, 8, 100.7540057, 0.004699248775403322), 

        (2010, 7, 100.2827521, 0.004721434971020463), 

        (2010, 6, 99.8114986, 0.002840908882540784), 

        (2010, 5, 99.5287465, 0.003802282101270077), 

        (2010, 4, 99.1517436, 0.008628954305672938), 

        (2010, 3, 98.3034873, 0.000959692827914721), 

        (2010, 2, 98.2092366, -0.002870813187265860), 

        (2010, 1, 98.4919887, Null); 

Go 

Select  * 

From    T_Madad 

Order By Shana, 

        Hodesh; 

Go

Select  *, 

        Madad/Lag(Madad,1) Over(Order By Shana,Hodesh)-1 Shinuy1 

From    T_Madad 

Order By Shana, 

        Hodesh; 

Select  *, 

        First_Value(Madad) Over(Order By Shana,Hodesh)*Exp(Sum(Log(1+IsNull(Shinuy,0))) 

                                                                Over(Order By Shana,Hodesh 

                                                                Rows Between Unbounded Preceding And Current Row)) Madad1 

From    T_Madad 

Order By Shana, 

        Hodesh;