sum value equally divided in dates

Question

Hi Expert,  
create table main2( oid int, OID2 int,SaleDateId date, quantity int,cons int)  
  
I am trying add calculated colum  as ccumulative which show divide the records order by date   
  
for an example 1204 has t records which shows 10  divided  sum of quantity is 2 (order by date)  
10/2=5   
create table main1( oid int, OID2 int,SaleDateId date, quantity int,cons int)  
  
  
truncate table main1  
  
insert main1  
values  
(5,1,20210407',1,100),  
(6,1,20210412',1,10),  
(7,2,20210412',1,10)  
  
  
  
expected output  
attached in image  
in the last column should be equally divided into 2 rows order by date which as shown in attachment  
  
i tried  
  
 ;WITH CTE AS  
 (SELECT *,SUM(quantity)OVER(PARTITION BY OID2 ORDER BY oid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SUM_quantity,  
           CAST((cons*1.0 / SUM(quantity)OVER(PARTITION BY OID2 ORDER BY oid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))AS decimal(10,2)) AS DEV_quantity  
  FROM main1  
 )  
 SELECT oid, OID2,SaleDateId, quantity,cons,   
        SUM(DEV_quantity)OVER(PARTITION BY OID2 ORDER BY oid DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cimulative_SUM  
 FROM CTE  
 ORDER BY oid   
  
but shows wrong records  

Expected ouput

Answer 1

Hi @Shambhu Rai
Try this：

SELECT *,CAST((cons*1.0 / SUM(quantity)OVER(PARTITION BY SaleDateId ORDER BY SaleDateId RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))AS decimal(10,2)) AS DEV_quantity  
FROM main1  

And for this issue :

done the casting part as well with cast(m.cons / c.NumberOfSameDay) as decimal(10,4) but result is changing and not coming as above

There is no need to convert the entire division algorithm into decimal, You just need to convert one of the divisors or dividends to decimal or float.Check the examples below:

SELECT 126/8 AS RESULT;  
SELECT CAST(126/8 AS decimal(10,4)) AS RESULT;  
  
SELECT CAST(126 AS float)/8 AS RESULT;--solution1  
  
SELECT 126*1.0/8 AS RESULT;--solution2  
SELECT CAST(126*1.0/8 AS decimal(10,2)) AS RESULT;  

Best regards,
LiHong

Answer 2

Try this:

;WITH CTE_Get_NumberOfSameDay AS (
SELECT SaleDateId, COUNT(SaleDateId) AS NumberOfSameDay
FROM main1
GROUP BY SaleDateId
)

SELECT m.*, m.cons / c.NumberOfSameDay AS Cimulative
FROM main1 AS m
INNER JOIN CTE_Get_NumberOfSameDay AS c ON m.SaleDateId = c.SaleDateId;

Answer 3

Hi Expert,
it is only accepting even numbers and not odd numbers equal distribution
for 126(8 rows) it shows 15 but it should shows 15.75

126-15
126-15
126-15
126-15
126-15
126-15
126-15
126-15