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 forecast

    FROM
    cteDate t
    LEFT OUTER JOIN #consumption c
    ON t.perioddate = c.period
    ORDER 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 forecast

    FROM
    cteDate t
    LEFT OUTER JOIN #consumption c
    ON t.perioddate = c.period
    ORDER 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!"

    Helpful Links:
    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;
    go
    CREATE TABLE #Consumption(
    MeterNo VARCHAR(10),
    PERIOD SMALLDATETIME,
    Amount MONEY);
    go

    INSERT 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;

    ;with
    data_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+1
    union all
    select * from proj_cte;

    Results

    n	month_period	Amount_agg	Amount_agg1	Amount_agg2
    1 2020-05-31 2000.00 0.00 0.00
    2 2020-06-30 2000.00 0.00 0.00
    3 2020-07-31 3000.00 0.00 0.00
    4 2020-08-31 3000.00 0.00 0.00
    5 2020-09-30 3000.00 0.00 0.00
    6 2020-10-31 3750.00 3000.00 3000.00
    7 2020-11-30 3000.00 3750.00 3000.00
    8 2020-12-30 3375.00 3000.00 3750.00
    9 2021-01-30 3375.00 3375.00 3000.00
    10 2021-02-28 3187.50 3375.00 3375.00
    11 2021-03-28 3375.00 3187.50 3375.00
    12 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 date
    DECLARE @MeterNo varchar(10)
    DECLARE @Period smalldatetime
    DECLARE @one_month_ago money
    DECLARE @two_months_ago money

    SET @max_month_to_calc = '20211231'

    DECLARE cursor_calc_forecast CURSOR LOCAL STATIC FOR
    SELECT MeterNo, MAX(PERIOD) AS Period
    FROM #Consumption
    GROUP BY MeterNo

    --DELETE FROM #Consumption WHERE Period > '20200831' --for testing, to allow re-runs

    OPEN cursor_calc_forecast

    WHILE 1 = 1
    BEGIN
    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_forecast

    SELECT *
    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 date
    DECLARE @MeterNo varchar(10)
    DECLARE @Period smalldatetime
    DECLARE @one_month_ago money
    DECLARE @two_months_ago money

    SET @max_month_to_calc = '20211231'

    DECLARE cursor_calc_forecast CURSOR LOCAL STATIC FOR
    SELECT MeterNo, MAX(PERIOD) AS Period
    FROM #Consumption
    GROUP BY MeterNo

    --DELETE FROM #Consumption WHERE Period > '20200831' --for testing, to allow re-runs

    OPEN cursor_calc_forecast

    WHILE 1 = 1
    BEGIN
    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_forecast

    SELECT *
    FROM #Consumption
    ORDER BY MeterNo, Period

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

You must be logged in to reply to this topic. Login to reply