sum equally distributed by date

Question

sum equally distributed by date

SSC-Addicted

Points: 497

March 4, 2022 at 3:02 pm

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 

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 whcich 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

@shree23 ,

Thank you for trying but you posted no image of expected results like your post says you did and I know for sure that you didn't even try your own test data code because it has two major errors in it that prevent it from running. How can you expect people to actually want to help you when you don't appear to actually care about the problem yourself.

Please repair your original post so that the test setup works and, please... only use code windows for code.

Yep... we could fix your stuff and give you an answer but, if you don't care enough to do even that small thing, why should we care to help?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)