Quantity divided by units

Shambhu Rai 1,411

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

Erland Sommarskog 107.2K Reputation points

2022-02-28T22:22:09.657+00:00

Sorry, but I am not able to understand the logic. Can you give more detail on how arrive at the value of 86.8 for the first row?

In the expected output the total of the units is 128.20, but the total of consumables in raito1 is 129. That's close, but not the same. Is there an error somewhere, or is there an explanation?

What is the significance of the quantity and cash columns in table main?

By the way, thanks for providing CREATE TABLE + INSERT! That helps a lot.
Shambhu Rai 1,411 Reputation points

2022-02-28T23:22:08.933+00:00

Hi,
here is the condition

on the 6th April -Main Table - 1 (quantity )sold and 73 consumables from Ratio Table on 7th April- means first quantity sold and then sold some consumable of the quantity - cons unit ( new column to be added)

so first row- 73

on 12 th 1 more quantity sold for which 10 consumables sold so 1+1 values will be divided by consumable 10 so 73+5 and 5*2 means 5

on 14th 3 quantity sold for which 36 consumable sold on so 1+1+3(quantity) divided by 36 consumable which is 7.2 so
first row 7.2+78- 85.2 (cons unit)
2nd row 5+ 7.2-12.2
3 row 7.2*2(quantity)=14.4
4th row 7.2*1(quantity)=7.2

on 16th 1 unit sold for which for which 1o consumable sold so 1+1+3+1=6(Quantity) divided by 10 means 1.6 so

first row 85.2 +1.6(cons unit)- 86.8
2nd row 12.2+1.6=13.8
3 row 14.2+1.6 *2=17.8
4th row 7.2+1.6=8.8
5th row- 1.6*1= 1.6
LiHong-MSFT 10,046 Reputation points

2022-03-04T06:46:57.313+00:00

Hi @Shambhu Rai
Please see my answer in your new thread.
And you will find two key steps :First is to cumulative calculate the quantity in ascending order of oid and then calculate the division;
Second is to cumulative calculate the divided value in descending order of oid.

By the way,Please remember to accept the answer if they helped for your initial question. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!

Best regards,
LiHong

14 answers

Shambhu Rai 1,411 Reputation points

2022-03-01T01:42:37.737+00:00

suggestion pls
Please sign in to rate this answer.
LiHong-MSFT 10,046 Reputation points

2022-03-01T02:52:05.917+00:00

Hi @Shambhu Rai
There are two things that i don't understand.
First,Please check the red area in this image. Are you sure it's ‘2021-04-21‘?Not ’2021-04-16‘？

Second, in the green area,How "73 consumables from Ratio Table on 7th April" can be connected with "the 6th April -Main Table"
Sign in to comment
Shambhu Rai 1,411 Reputation points

2022-03-01T03:07:05.68+00:00

yes quantity purchased on 16-4 and consumable purchased on 21-04
everytime quantity will be purchased first and then consumable will be purchased on the same day or the nearest day available order by unit 1
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Shambhu Rai 1,411 Reputation points

2022-03-01T05:14:25.187+00:00

suggestion pls
Please sign in to rate this answer.
LiHong-MSFT 10,046 Reputation points

2022-03-01T06:05:13.213+00:00

Hi @Shambhu Rai
Please check the yellow area in your result ,where I think should be :"14.4+1.6*2=17.6"

LiHong-MSFT 10,046 Reputation points

2022-03-01T06:16:17.167+00:00

If I am right, please check my answer below.
Sign in to comment

LiHong-MSFT 10,046

Hi @Shambhu Rai
Please check this query:

;WITH CTE1 AS  
(SELECT UNIT1,SaleDateId,CAST(SUM(consumables)AS FLOAT)AS SUM_Consumables,ROW_NUMBER()OVER(PARTITION BY UNIT1 ORDER BY SaleDateId)AS RowNum  
 FROM #ratio1  
 GROUP BY UNIT1,SaleDateId  
),CTE2 AS  
(SELECT  UNIT1,SaleDateId,SUM(SUM(quantity))OVER(PARTITION BY UNIT1 ORDER BY SaleDateId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS SUM_Quantity  
              ,DENSE_RANK()OVER(PARTITION BY UNIT1 ORDER BY SaleDateId) AS RankNum  
 FROM #main  
 GROUP BY UNIT1,SaleDateId  
),CTE3 AS  
( SELECT C1.UNIT1,C2.SaleDateId,FLOOR(SUM_Consumables/SUM_Quantity*10)/10 AS Dev_Consumables,  
       SUM(FLOOR(SUM_Consumables/SUM_Quantity*10)/10)OVER(PARTITION BY C1.UNIT1  ORDER BY C1.SaleDateId DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Unit  
 FROM  CTE1 C1 JOIN CTE2 C2 ON  C1.UNIT1=C2.UNIT1 AND C1.RowNum=C2.RankNum   
)  
SELECT oid,M.UNIT1,M.SaleDateId,M.quantity,M.cash AS cashCons,C3.Unit*quantity AS Unit  
FROM #main M JOIN CTE3 C3 ON M.UNIT1=C3.UNIT1 AND M.SaleDateId=C3.SaleDateId  
ORDER BY SaleDateId

Output:

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Shambhu Rai 1,411 Reputation points

2022-03-01T15:40:21.177+00:00

could you please explain this query and what it is doing

CTE2 AS
(SELECT UNIT1,SaleDateId,SUM(SUM(quantity))OVER(ORDER BY SaleDateId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS SUM_Quantity
,DENSE_RANK()OVER(PARTITION BY UNIT1 ORDER BY SaleDateId) AS RankNum
FROM #main
GROUP BY UNIT1,SaleDateId
),CTE3 AS
( SELECT C1.UNIT1,C2.SaleDateId,FLOOR(SUM_Consumables/SUM_Quantity*10)/10 AS Dev_Consumables,
SUM(FLOOR(SUM_Consumables/SUM_Quantity*10)/10)OVER(ORDER BY C1.SaleDateId DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

and where i should filter the UNIT1=4444444 where we will have more unit1
Please sign in to rate this answer.
Erland Sommarskog 107.2K Reputation points

2022-03-01T22:21:13.913+00:00

This piece:

SUM(SUM(quantity))OVER(ORDER BY SaleDateId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS SUM_Quantity

Gives you the accumulated sum per unit and date. Had it just said SUM(Quantity), it would only have returned the sale per day. To get the accumulated sum, Li wraps it the day sum in an outer SUM which has an OVER clause. But I think there is an error. I think it should be:

SUM(SUM(quantity))OVER(PARTITION BY UNIT1 ORDER BY SaleDateId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS SUM_Quantity

So that you get the sum per unit. (Since you only had one unit in the test data, I can see how Li made this slip.)

As for CTE3, my head starts to spin a little. I still have not been able to understand the business rules. Apparently Li had more time on his hands than I had to analyse the problem. But think the OVER clause in CTE3 is also missing a partition clause.

LiHong-MSFT 10,046 Reputation points

2022-03-02T01:17:56.507+00:00

Thanks Erland for your explanation and remind. Since there is only one UNIT in the test data, so I forgot to add PARTITION BY, it's my mistake.And I've edited the answer above.

LiHong-MSFT 10,046 Reputation points

2022-03-02T02:03:17.133+00:00

Hi @Shambhu Rai
For CTE2, as Erland said, the SUM(SUM(quantity))OVER() gives you the accumulated sum per unit and date.
Since there are the same two dates '20210414' in your main table, if you use SUM (quantity)OVER(ORDER BY SaleDateId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ,
you will get: which has two different SUM_Quantity in '20210414'
Therefore, we need to SUM the quantity in each date first, then do cumulative sum SUM(SUM(quantity))OVER(...).
If you are confused about ”ROWS BETWEEN“，please refer to this article for more details and some examples: 5 Practical Examples of Using ROWS BETWEEN in SQL

For CTE3, Join the two result tables of CTE1 and CTE2 and calculate the division as shown in yellow area.Then do cumulative sum to get this

For this：”and where i should filter the UNIT1=4444444 where we will have more unit1“
It can be added in the outer query，like this:

SELECT oid,M.UNIT1,M.SaleDateId,M.quantity,M.cash AS cashCons,C3.Unit*quantity AS Unit FROM #main M JOIN CTE3 C3 ON M.UNIT1=C3.UNIT1 AND M.SaleDateId=C3.SaleDateId WHERE M.UNIT1=4444444 ORDER BY SaleDateId

Erland Sommarskog 107.2K Reputation points

2022-03-02T22:04:12.593+00:00

Great explanation Li! It must have taken some time to compose those images!
Sign in to comment

Share via

Quantity divided by units

14 answers