# query to calculated based on the value of previous month

• Dear SQL Expert,

I want to make a query to calculate the amount for budget purposes.

Here's the formula :

Actual 2020    Forecast 2020

Jul     Aug        Sep     Oct     Nov   Dec

2000 2000     3000 3000 3000 3000

Sep=((Jul+Aug)/2)/8*12

Oct=(((Aug/8)*12)+Sep)/2

Nov=(Sep+Oct)/2

Dec=(Oct+Nov)/2

Jan=(Nov+Dec)/2

Feb=(Dec+Jan)/2

etc..

from nov and so on, we only calculate average 2 months before..

only for sep & oct which has different formula.

``` CREATE TABLE #Consumption (MeterNo VARCHAR(10), Period SMALLDATETIME, Amount MONEY) INSERT INTO #Consumption VALUES ('001','20200531',2000) INSERT INTO #Consumption VALUES ('001','20200630',2000) INSERT INTO #Consumption VALUES ('001','20200731',2000) INSERT INTO #Consumption VALUES ('001','20200831',2000) ```

Please create a query to add more records from sep 2020 until dec 2021 based on the formula

Thanks

• Anyone?

• So, I broke this down a bit, using a tally table to project to the future. I think all your dates after n= 5 will be the same, but you'd have to test a bit. I add dates from your current date, which is likely today - 2 months, so you can adjust that if needed. Then I apply your formula

`WITH myTally (n)AS (   SELECT n = ROW_NUMBER() OVER (ORDER BY                                         (SELECT NULL))       FROM              (   VALUES                      (1)                    , (2)                    , (3)                    , (4)                    , (5)                    , (6)                    , (7)                    , (8)                    , (9)                    , (10)) a (n) )   , cteDate (n, perioddate)AS (   SELECT            n          , EOMONTH(DATEADD(MONTH, n, '2020/6/30')) AS perioddate       FROM myTally)SELECT                    t.perioddate AS months                  , CASE                        WHEN n < 3 THEN                            amount                        WHEN n = 3 THEN                  (((LAG(amount, 2) OVER (ORDER BY perioddate)                     + LAG(amount, 1) OVER (ORDER BY perioddate)) / 2) / 8                   * 12)                        WHEN n = 4 THEN                  (((LAG(amount, (2)) OVER (ORDER BY t.perioddate) / 8) * 12)                   + (((LAG(amount, (3)) OVER (ORDER BY t.perioddate)                        + LAG(amount, (2)) OVER (ORDER BY t.perioddate)) / 2) / 8                      * 12))      / 2                        WHEN n = 5 THEN                  (((LAG(amount, 3) OVER (ORDER BY t.perioddate) / 8) * 12)                   + (((LAG(amount, 4) OVER (ORDER BY t.perioddate)                        + LAG(amount, 3) OVER (ORDER BY t.perioddate)) / 2) / 8                      * 12))      / 2                    END AS forecastFROM                    cteDate t    LEFT OUTER JOIN #consumption c        ON t.perioddate = c.periodORDER BY            months;`
• Steve Jones - SSC Editor wrote:

So, I broke this down a bit, using a tally table to project to the future. I think all your dates after n= 5 will be the same, but you'd have to test a bit. I add dates from your current date, which is likely today - 2 months, so you can adjust that if needed. Then I apply your formula

`WITH myTally (n)AS (   SELECT n = ROW_NUMBER() OVER (ORDER BY                                         (SELECT NULL))       FROM              (   VALUES                      (1)                    , (2)                    , (3)                    , (4)                    , (5)                    , (6)                    , (7)                    , (8)                    , (9)                    , (10)) a (n) )   , cteDate (n, perioddate)AS (   SELECT            n          , EOMONTH(DATEADD(MONTH, n, '2020/6/30')) AS perioddate       FROM myTally)SELECT                    t.perioddate AS months                  , CASE                        WHEN n < 3 THEN                            amount                        WHEN n = 3 THEN                  (((LAG(amount, 2) OVER (ORDER BY perioddate)                     + LAG(amount, 1) OVER (ORDER BY perioddate)) / 2) / 8                   * 12)                        WHEN n = 4 THEN                  (((LAG(amount, (2)) OVER (ORDER BY t.perioddate) / 8) * 12)                   + (((LAG(amount, (3)) OVER (ORDER BY t.perioddate)                        + LAG(amount, (2)) OVER (ORDER BY t.perioddate)) / 2) / 8                      * 12))      / 2                        WHEN n = 5 THEN                  (((LAG(amount, 3) OVER (ORDER BY t.perioddate) / 8) * 12)                   + (((LAG(amount, 4) OVER (ORDER BY t.perioddate)                        + LAG(amount, 3) OVER (ORDER BY t.perioddate)) / 2) / 8                      * 12))      / 2                    END AS forecastFROM                    cteDate t    LEFT OUTER JOIN #consumption c        ON t.perioddate = c.periodORDER BY            months;`

Lag isn't available in 2008. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• deleted

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• yes, this query can't be run on 2008..

• Steve Collins wrote:

Here's my partly done solution.  It needs CROSS APPLY the projections

`drop table if exists #Consumption;goCREATE TABLE #Consumption(  MeterNo VARCHAR(10),  PERIOD SMALLDATETIME,  Amount MONEY);goINSERT INTO #Consumption VALUES('001','20200531',2000),('001','20200630',2000),('001','20200731',2000),('001','20200831',2000);declare @start_yr           int=2020,        @start_mo           int=5;select * from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) v(n)     cross apply     (select dateadd(d, -1, dateadd(m, v.n, cast(convert(char(4), @start_yr) + '-' + right('0'+cast(@start_mo as varchar(2)), 2) + '-01' as date))) month_period) dt     left join     #Consumption c1 on dt.month_period=c1.[PERIOD]     left join #Consumption c2 on c1.MeterNo=c2.MeterNo                                  and datediff(m, c1.[Period], c2.[Period])=-1     left join #Consumption c3 on c1.MeterNo=c3.MeterNo                                  and datediff(m, c1.[Period], c3.[Period])=-2;`

why the amount starting from september and so on not calculated ?

the unique formula is only for september & october

• september is average amount from 2 months before (july & august) and divided by 8 then multiply by 12
• october is average amount from september which already calculated before and (august amount divided by 8 and multiply by 12)
• october and so on is only average from 2 months before which already calculated, for example november taking from average september & october which we already get from previous formula, december is average from octiber & november, and so on...

here's the formula to calculated amounts for each month.

Sep=((Jul+Aug)/2)/8*12

Oct=(((Aug/8)*12)+Sep)/2

Nov=(Sep+Oct)/2

Dec=(Oct+Nov)/2

Jan=(Nov+Dec)/2

Feb=(Dec+Jan)/2

so on...

This query should represented all amount & period starting from september until december next year and grouping by MeterNo. I need to group by MeterNo as the source table has more than 1 MeterNo.

Thanks

• This reply was modified 2 months, 3 weeks ago by  gentong.bocor.
• gentong.bocor wrote:

yes, this query can't be run on 2008..

You mean the queries posted here, or it's not possible at all?  There's got to be a way

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• Steve Collins wrote:

gentong.bocor wrote:

yes, this query can't be run on 2008..

You mean the queries posted here, or it's not possible at all?  There's got to be a way

I mean, the query from Steve Jones can't be run on 2008. It has function from SQL 2012 which not supported.

• This reply was modified 2 months, 3 weeks ago by  gentong.bocor.
• This was removed by the editor as SPAM

• gentong.bocor wrote:

why the amount starting from september and so on not calculated ?

This query should represented all amount & period starting from september until december next year and grouping by MeterNo. I need to group by MeterNo as the source table has more than 1 MeterNo.

Thanks

Sorry about that.  It was late and yesterday was a looong day.   Before adding the complexity of forecasting PER METER (which might be excessive) please have a look at this code.  I think it does what you're looking for.  For now it ignores the MeterNo.  If it does what you want it could be generalized.

`declare @start_yr           int=2020,        @start_mo           int=5;;withdata_rn_cte as (    select v.n, dt.month_period, coalesce(calc1.c_calc1, C1.Amount) Amount_agg, row_number() over (order by v.n asc) as rownum    from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) v(n)         cross apply         (select dateadd(d, -1, dateadd(m, v.n, cast(convert(char(4), @start_yr) + '-' + right('0'+cast(@start_mo as varchar(2)), 2) + '-01' as date))) month_period) dt         left join         #Consumption c1 on dt.month_period=c1.[PERIOD]         left join #Consumption c2 on /*c1.MeterNo=c2.MeterNo                                      and*/ datediff(m, dt.month_period, c2.[Period])=-1         left join #Consumption c3 on /*c1.MeterNo=c3.MeterNo                                      and*/ datediff(m, dt.month_period, c3.[Period])=-2         cross apply         (select ((c2.Amount+c3.Amount)/2)/8*12 c_calc1) calc1),trans_cte(n, month_period, Amount_agg, Amount_agg1, Amount_agg2) as (    select dr1.n, dr1.month_period, coalesce(dr1.Amount_agg, (((dr_lag2.Amount_agg/8)*12)+dr_lag1.Amount_agg)/2), 0, 0    from data_rn_cte dr1         outer apply (select top 1 Amount_agg, month_period from data_rn_cte dr2 where dr1.n=dr2.n+1 order by dr1.month_period) dr_lag1         outer apply (select top 1 Amount_agg, month_period from data_rn_cte dr3 where dr1.n=dr3.n+2  order by dr1.month_period) dr_lag2),trans_last_cte(n, month_period, Amount_agg, Amount_agg1, Amount_agg2) as (    select tc.n, tc.month_period, tc.Amount_agg, tc1.Amount_agg Amount_agg1, tc2.Amount_agg Amount_agg2    from trans_cte tc         left join trans_cte tc1 on tc.n=tc1.n+1         left join trans_cte tc2 on tc.n=tc2.n+2    where tc.n=@start_mo+1),proj_cte as (    select * from trans_last_cte    union all    select pc.n+1 n, dateadd(m, 1, pc.month_period), (pc.Amount_agg1+pc.Amount_agg2)/2,  pc.Amount_agg Amount_agg1, PC.Amount_agg1 Amount_agg2    from proj_cte pc    where pc.n<12)select * from trans_cte where n<@start_mo+1union allselect * from proj_cte;`

Results

`n	month_period	Amount_agg	Amount_agg1	Amount_agg21	2020-05-31	2000.00	        0.00	        0.002	2020-06-30	2000.00	        0.00	        0.003	2020-07-31	3000.00	        0.00	        0.004	2020-08-31	3000.00	        0.00	        0.005	2020-09-30	3000.00	        0.00	        0.006	2020-10-31	3750.00	        3000.00	        3000.007	2020-11-30	3000.00	        3750.00	        3000.008	2020-12-30	3375.00	        3000.00	        3750.009	2021-01-30	3375.00	        3375.00	        3000.0010	2021-02-28	3187.50	        3375.00	        3375.0011	2021-03-28	3375.00	        3187.50	        3375.0012	2021-04-28	3281.25	        3375.00	        3187.50`

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• gentong.bocor wrote:

Steve Collins wrote:

gentong.bocor wrote:

yes, this query can't be run on 2008..

You mean the queries posted here, or it's not possible at all?  There's got to be a way

I mean, the query from Steve Jones can't be run on 2008. It has function from SQL 2012 which not supported.

He has EOMONTH in there too and that's even newer.  But his code is probably the most useful piece of information in this thread.  The OP should be looking for an upgrade path instead of new query 🙂

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• My apologies, I neglected to look at the forum. Most of the T-SQL is from 2012+ (LAG/LEAD). EOMONTH is 2014+

• I have to admit, in this situation I'd likely just use a cursor and a loop, something like below, just because of the complexity, and likely overhead, of recursion in this case.

`DECLARE @max_month_to_calc dateDECLARE @MeterNo varchar(10)DECLARE @Period smalldatetimeDECLARE @one_month_ago moneyDECLARE @two_months_ago moneySET @max_month_to_calc = '20211231'DECLARE cursor_calc_forecast CURSOR LOCAL STATIC FORSELECT MeterNo, MAX(PERIOD) AS PeriodFROM #ConsumptionGROUP BY MeterNo--DELETE FROM #Consumption WHERE Period > '20200831' --for testing, to allow re-runsOPEN cursor_calc_forecastWHILE 1 = 1BEGIN    FETCH NEXT FROM cursor_calc_forecast INTO @MeterNo, @Period    IF @@FETCH_STATUS <> 0        BREAK;    WHILE 2 = 2        BEGIN        SET @Period = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Period) + 2, 0))        IF @Period > @max_month_to_calc            BREAK;        SELECT @one_month_ago = Amount        FROM #Consumption        WHERE MeterNo = @MeterNo AND Period = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Period), 0))        SELECT @two_months_ago = Amount        FROM #Consumption        WHERE MeterNo = @MeterNo AND Period = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Period) - 1, 0))                INSERT INTO #Consumption        SELECT @MeterNo, @Period,            CASE WHEN MONTH(@Period) = 9 THEN ((@two_months_ago + @one_month_ago)/2)/8*12                 WHEN MONTH(@Period) = 10 THEN (((@two_months_ago/8)*12)+@one_month_ago)/2                  ELSE (@two_months_ago + @one_month_ago) / 2                 END    END /*WHILE*/END /*WHILE*/DEALLOCATE cursor_calc_forecastSELECT * FROM #Consumption ORDER BY MeterNo, Period`

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

• okay..

i will take this query for my program, without consider this query is not recursive. this just using normal loop. if can made recursive it will be better.

Thanks..

ScottPletcher wrote:

I have to admit, in this situation I'd likely just use a cursor and a loop, something like below, just because of the complexity, and likely overhead, of recursion in this case.

`DECLARE @max_month_to_calc dateDECLARE @MeterNo varchar(10)DECLARE @Period smalldatetimeDECLARE @one_month_ago moneyDECLARE @two_months_ago moneySET @max_month_to_calc = '20211231'DECLARE cursor_calc_forecast CURSOR LOCAL STATIC FORSELECT MeterNo, MAX(PERIOD) AS PeriodFROM #ConsumptionGROUP BY MeterNo--DELETE FROM #Consumption WHERE Period > '20200831' --for testing, to allow re-runsOPEN cursor_calc_forecastWHILE 1 = 1BEGIN    FETCH NEXT FROM cursor_calc_forecast INTO @MeterNo, @Period    IF @@FETCH_STATUS <> 0        BREAK;    WHILE 2 = 2        BEGIN        SET @Period = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Period) + 2, 0))        IF @Period > @max_month_to_calc            BREAK;        SELECT @one_month_ago = Amount        FROM #Consumption        WHERE MeterNo = @MeterNo AND Period = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Period), 0))        SELECT @two_months_ago = Amount        FROM #Consumption        WHERE MeterNo = @MeterNo AND Period = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Period) - 1, 0))                INSERT INTO #Consumption        SELECT @MeterNo, @Period,            CASE WHEN MONTH(@Period) = 9 THEN ((@two_months_ago + @one_month_ago)/2)/8*12                 WHEN MONTH(@Period) = 10 THEN (((@two_months_ago/8)*12)+@one_month_ago)/2                  ELSE (@two_months_ago + @one_month_ago) / 2                 END    END /*WHILE*/END /*WHILE*/DEALLOCATE cursor_calc_forecastSELECT * FROM #Consumption ORDER BY MeterNo, Period`

Viewing 15 posts - 1 through 15 (of 16 total)