sum value equally divided in dates

Shambhu Rai 1,406 Reputation points
2022-03-04T15:48:50.51+00:00
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

180203-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,895 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,826 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHong-MSFT 10,046 Reputation points
    2022-03-07T02:02:03.637+00:00

    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

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points
    2022-03-04T16:25:12.967+00:00

    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;
    

  2. Shambhu Rai 1,406 Reputation points
    2022-03-04T20:20:54.417+00:00

    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

    0 comments No comments