September 1, 2020 at 7:45 am
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
September 1, 2020 at 12:11 pm
Anyone?
September 1, 2020 at 5:15 pm
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;
September 1, 2020 at 9:07 pm
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
Change is inevitable... Change for the better is not.
September 1, 2020 at 11:25 pm
deleted
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 2, 2020 at 12:46 am
yes, this query can't be run on 2008..
September 2, 2020 at 12:54 am
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
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
September 2, 2020 at 4:39 am
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.
September 2, 2020 at 12:39 pm
This was removed by the editor as SPAM
September 2, 2020 at 1:44 pm
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
nmonth_periodAmount_aggAmount_agg1Amount_agg2
12020-05-312000.00 0.00 0.00
22020-06-302000.00 0.00 0.00
32020-07-313000.00 0.00 0.00
42020-08-313000.00 0.00 0.00
52020-09-303000.00 0.00 0.00
62020-10-313750.00 3000.00 3000.00
72020-11-303000.00 3750.00 3000.00
82020-12-303375.00 3000.00 3750.00
92021-01-303375.00 3375.00 3000.00
102021-02-283187.50 3375.00 3375.00
112021-03-283375.00 3187.50 3375.00
122021-04-283281.25 3375.00 3187.50
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 2, 2020 at 2:06 pm
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
September 2, 2020 at 3:47 pm
My apologies, I neglected to look at the forum. Most of the T-SQL is from 2012+ (LAG/LEAD). EOMONTH is 2014+
September 2, 2020 at 6:48 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
September 7, 2020 at 1:09 am
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..
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 15 total)
You must be logged in to reply to this topic. Login to reply