suggestion pls
Quantity divided by units
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:
1quantity 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 06042021 1 200 86.8
34343 44444444 12042021 1 200 13.8
34343 44444444 14042021 2 200 17.4
34343 44444444 14042021 1 200 8.8
34343 44444444 16042021 1 200 1.6
14 answers
Sort by: Most helpful


Shambhu Rai 1,406 Reputation points
20220301T03:07:05.68+00:00 yes quantity purchased on 164 and consumable purchased on 2104
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 

LiHongMSFT 10,046 Reputation points
20220301T06:14:32.767+00:00 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 email notifications if you want to receive the related email notification for this thread. 
Shambhu Rai 1,406 Reputation points
20220301T15: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