Quantity divided by units

Question

Hi Expert,

I am facing challanges for logic to apply all previous units sold divided by next or nearest dates quantity
here is the query

Create table ratio1 (UNIT1 int,CONS integer,SaleDateId date,consumables  integer)

insert ratio1
values
(4444444,222,'20210407',10),
(4444444,333,'20210407',15),
(4444444,444,'20210407',24),
(4444444,555,'20210407',24),
(4444444,777,'20210412',10),
(4444444,888,'20210414',16),
(4444444,999,'20210414',20),
(4444444,000,'20210421',10)


=========================================

create table main ( oid int, UNIT1 int,SaleDateId date, quantity int, cash int)
--truncate  table main
insert main
values (343434,4444444,'20210406',1,200),
(343434,4444444,'20210412',1,200),
(343434,4444444,'20210414',2,200),
(343434,4444444,'20210414',1,200),
(343434,4444444,'20210416',1,200)


The units divide by quantity  and order by date  so for dtd 07 April Agreegation will happen and after that it will be equally distributed for all previous orders
rough calculation:
1-quantity purchased on 20210406 from unit1  from Table Main
on the 7th April from consuambles sold for same unit1 73 cons unit( new column to be added)
 on 12 th 2 more unit1 sold for which 10 cons units sold so values will  be devided by quantiy euqally so 73+5 and 5*2 means 10 this calculation will go further

how to give logic for it. I am newbie

Expected output


Expected output
oid    UNIT1     SaleDateId      Quantity  cashCons Unit
343434   4444444  06-04-2021     1        200     86.8
34343    44444444  12-04-2021    1        200     13.8
34343    44444444  14-04-2021    2        200     17.4
34343     44444444 14-04-2021    1        200     8.8
34343    44444444  16-04-2021    1        200      1.6

Answer 1

suggestion please

Answer 2

suggestion please

Answer 3

Suggestion pls

Answer 4

Suggestion pls