Quantity divided by units

Shambhu Rai 1,406 Reputation points
2022-02-28T21:14:51.023+00:00
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
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
{count} votes

14 answers

Sort by: Most helpful
  1. Shambhu Rai 1,406 Reputation points
    2022-03-01T01:42:37.737+00:00

    suggestion pls


  2. Shambhu Rai 1,406 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

    0 comments No comments

  3. Shambhu Rai 1,406 Reputation points
    2022-03-01T05:14:25.187+00:00

    suggestion pls


  4. LiHong-MSFT 10,046 Reputation points
    2022-03-01T06: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:
    178710-image.png

    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.

    0 comments No comments

  5. Shambhu Rai 1,406 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