SALES_DATE DATE,

CURR_DATE_UNITS_PURCHASED DECIMAL(38,10),

LTD_UNITS_PURCHASED DECIMAL(38,10),

CURR_DATE_EXTRAP_NUM_SOLD DECIMAL(38,10),

LTD_EXTRAP_NUM_SOLD DECIMAL(38,10)

);

INSERT INTO example_data_expected VALUES

('2023-11-01', 1000, 1000, 100, 100), -- 1,000 left

('2023-11-02', 0, 1000, 90, 190), -- 900 left (1,000 - 100)

('2023-11-03', 0, 1000, 81, 271), -- 810 left (1,000 - 190)

('2023-11-04', 200, 1200, 92.9, 363.9), -- 929 left (1,200 - 271)

('2023-11-05', 0, 1200, 83.6, 447.5), -- 863.1 left (1,200 - 363.9)

('2023-11-06', 0, 1200, 75.3, 522.8), -- 752.5 left (1,200 - 447.5)

('2023-11-07', 50, 1250, 72.7, 595.5), -- 727.2 left (1,250 - 522.8)

('2023-11-08', 0, 1250, 65.5, 661.0); -- 654.5 left (1,250 - 595.5)

INSERT INTO example_data_expected VALUES

('2023-11-09', 0, 1250, 58.906521, 719.841311)

,('2023-11-10', 0, 1250, 53.0158689, 772.8571799)

,('2023-11-11', 0, 1250, 47.71428201, 820.5714619)

;

,cte1 as(

select t.*

,row_number()over(order by sales_date) k

,sum(CURR_DATE_UNITS_PURCHASED)over(order by sales_date) roll_sum_purchased

,params.x

from example_data_expected t

cross apply params

)

,cte2 as(

select *

,roll_sum_purchased

-(sum(CURR_DATE_UNITS_PURCHASED*power(x,1-k))over(order by sales_date))*power(x,k) extrap_sum

from cte1

)

select SALES_DATE,k,roll_sum_purchased

,extrap_sum-lag(extrap_sum,1,0)over(order by SALES_DATE) curr_day_extrap

,CURR_DATE_EXTRAP_NUM_SOLD

,extrap_sum

,LTD_EXTRAP_NUM_SOLD

,LTD_EXTRAP_NUM_SOLD - extrap_sum check_extrap_sum

,CURR_DATE_UNITS_PURCHASED

from cte2

with base_cte as

(

select min(SALES_DATE) as minDate , max(SALES_DATE) as maxdate

from example_data_expected

) ,

recursive_cte as

(

select

SALES_DATE ,

CURR_DATE_UNITS_PURCHASED ,

LTD_UNITS_PURCHASED ,

cast(CURR_DATE_UNITS_PURCHASED as decimal(10,2)) as PREV_DAY_LTD_EXTRAP ,

cast(CURR_DATE_UNITS_PURCHASED*0.1 as decimal(10,1)) as

CURR_DATE_EXTRAP_NUM_SOLD

from example_data_expected where SALES_DATE = (select minDate from base_cte)

union all

select

b.SALES_DATE ,

b.CURR_DATE_UNITS_PURCHASED ,

b.LTD_UNITS_PURCHASED ,

cast(a.PREV_DAY_LTD_EXTRAP+b.CURR_DATE_UNITS_PURCHASED-a.CURR_DATE_EXTRAP_NUM_SOLD as decimal(10,2)) as PREV_DAY_LTD_EXTRAP ,

cast((a.PREV_DAY_LTD_EXTRAP+b.CURR_DATE_UNITS_PURCHASED-a.CURR_DATE_EXTRAP_NUM_SOLD)*0.1 as decimal(10,1)) as CURR_DATE_EXTRAP_NUM_SOLD

from recursive_cte as a inner join example_data_expected as b on dateadd(day,1,a.SALES_DATE) = b.SALES_DATE

where b.SALES_DATE <= (select maxdate from base_cte)

)

select SALES_DATE , CURR_DATE_UNITS_PURCHASED , LTD_UNITS_PURCHASED , CURR_DATE_EXTRAP_NUM_SOLD ,

sum(CURR_DATE_EXTRAP_NUM_SOLD) over(order by SALES_DATE) as LTD_EXTRAP_NUM_SOLD

from recursive_cte;

select n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

,(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)

,(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)

,(31),(32),(33),(34),(35),(36),(37),(38),(39),(40)

,(41),(42),(43),(44),(45),(46),(47),(48),(49),(50)

,(51),(52),(53),(54),(55),(56),(57),(58),(59),(60)

,(61),(62),(63),(64),(65),(66),(67),(68),(69),(70)

,(71),(72),(73),(74),(75),(76),(77),(78),(79),(80)

)t(n)

)

select *

,power(cast(0.9 as float),n) powern

,cast(1000.0 as float)-cast(1000.0 as float)*power(cast(0.9 as float),n) pn1000

,cast(1000.0 as float)*power(cast(0.9 as float),n) pn1000rest

from nums