locked
Write a query RRS feed

  • Question

  • I need to write a query which should not be too complicated.
    I have requests for product in table "StockRequest".
    Then I have table "Stock" where is stock for each product.

    For example lets take product A:

    for product A there is 50 pieces on stock and packing unit is 2.

    Now I need to go over each request and add by 2 to each request for product A. When I come to the last one, i start again

    and loop over each request and add 2 and then again, until whole request is satisfied or product A is out of stock.

    Here is data and result :

    /*
    IF EXISTS(SELECT * FROM sys.tables AS t WHERE t.name='StockRequest')
    	DROP TABLE [dbo].[StockRequest];
    GO
    
    IF EXISTS(SELECT * FROM sys.tables AS t WHERE t.name='Stock')
    	DROP TABLE [dbo].[Stock];
    GO
    
    CREATE TABLE [dbo].[StockRequest](
    	[request_id] [int] NOT NULL,
    	[product_id] [char](1) NOT NULL,
    	[requiredQ] [int] NOT NULL,
     CONSTRAINT [PK_StockRequest] PRIMARY KEY CLUSTERED 
    ([request_id] ASC)) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[Stock](
    	[prod_id] [char](1) NOT NULL,
    	[stock_q] [int] NOT NULL,
    	[pack_unit] int NOT NULL
     CONSTRAINT [PK_Stock] PRIMARY KEY CLUSTERED 
    (prod_id ASC)) ON [PRIMARY]
    GO
    
    INSERT INTO dbo.StockRequest ( request_id, product_id, requiredQ )
    VALUES  ( 1,'A',1500),( 2,'A',6),( 3,'A',10), (4,'A', 100)
    
    INSERT INTO dbo.StockRequest ( request_id, product_id, requiredQ )
    VALUES  ( 5,'B',100),( 6,'B',500),( 7,'B',50), (8,'B', 4)
    
    INSERT INTO dbo.Stock ( prod_id, stock_q, pack_unit )
    VALUES  ( 'A', 50, 2 ), ('B', 6, 1)
    */
    
    --I need TO ADD each request packing unit until request is completed or available q is consumed.
    
    --The END RESULT should be:
    SELECT 1 AS requestId, 1500 AS qRequired, 18 AS qAdd
    UNION ALL
    SELECT 2 AS requestId, 6 AS qRequired, 6 AS qAdd
    UNION ALL
    SELECT 3 AS requestId, 10 AS qRequired, 10 AS qAdd
    UNION ALL
    SELECT 4 AS requestId, 100 AS qRequired, 16 AS qAdd
    UNION ALL
    SELECT 5 AS requestId, 100 AS qRequired, 2 AS qAdd
    UNION ALL
    SELECT 6 AS requestId, 500 AS qRequired, 2 AS qAdd
    UNION ALL
    SELECT 7 AS requestId, 50 AS qRequired, 1 AS qAdd
    UNION ALL
    SELECT 8 AS requestId, 4 AS qRequired, 1 AS qAdd
    Can someone write a query without cursor?

    Tuesday, June 7, 2016 1:00 PM

All replies

  • It is unclear what is the logic behind the scene. 

    select *,
    SUM(pack_unit) OVER(PARTITION BY st.product_id
                    ORDER BY request_id
                    ROWS BETWEEN UNBOUNDED PRECEDING
                             AND CURRENT ROW) AS runqty from StockRequest st join stock s
    on st.product_id=s.prod_id


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, June 7, 2016 1:18 PM
    Answerer
  • The logic is very simple, maybe I don't explain well. Will try with more basic example.

    For example, you have ordered 100 pieces of product A and I have ordered 4 pieces of product A. But the stock is only 14. The packing unit is 2 (someone can order 2 pieces, 4 pieces, and so on... you can't order one piece or 3 pieces and so on..).

    Now the query should give 1 packing unit to you and 1 packing unit to me.
    We have each 2 pieces and the remaining stock is 10, so, we can get more.
    The query goes again and give 1 packing unit to you and 1 packing unit to me.
    Now we have each 4 pieces and remaining stock is 6. My request is finished, since I have ordered only 4.
    So, you get all remaining stock, which is 6.

    At the end, i get 4 pieces and you get 10 pieces.

    Is it more understandable? Thank you for your help.


    • Edited by simonxy Tuesday, June 7, 2016 1:35 PM
    Tuesday, June 7, 2016 1:34 PM
  • Uri Dimant's great solution is the most simple query you can write. It applies from SQL 2012 forward

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    Tuesday, June 7, 2016 1:37 PM
  • paSQuale - the query is great, but it doesn't give required result :)
    Tuesday, June 7, 2016 1:41 PM
  • Not so much, lets take a prod A, you have requieredQ  =1500 , how do you get 18 as qAdd???


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, June 7, 2016 1:46 PM
    Answerer
  • I will explain. There are 4 requests for product A. The steps are:

    1. You add 2(packing unit) to each request(number of requests=4) for product A. Remaining stock is 42.

    2. You add 2 to each request(4) for product A. Remaining stock is 34.

    3. You add 2 to each request(4) for product A. Remaining stock is 26. The request_id=2 is now completed(since it required only 6 pieces), so, you have remaining only 3 requests.

    4. You add 2 to each request(3) for product A. Remaining stock is 20. 

    5. You add 2 to each request(3) for product A. Remaining stock is 14. The request_id=3 is now completed, since it has 10 pieces, so, from now on, you have remaining only 2 requests.

    6. You add 2 to each request(2) for product A. Remaining stock is 10.

    7. You add 2 to each request(2) for product A. Remaining stock is 6.

    6. You add 2 to each request(2) for product A. Remaining stock is 2.

    7. You add 2 to first request, and 0 to second, since there is not enough stock to satisfy both. And since stock is 0 now, the query is finished. 

    And the final result is as described in beginning.


    • Edited by simonxy Tuesday, June 7, 2016 1:58 PM
    Tuesday, June 7, 2016 1:58 PM
  • So 50-8=42-8=34-8=26... I see a value qAdd for request2 =6 , How did you get 6, 26-? or what? 

    Value for request 1 is 18.....?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, June 7, 2016 2:29 PM
    Answerer
  • At each loop each request gets one packing unit, until stock is empty or request is fulfilled.

    So, at first loop stock is 50-8=42
    and requests are:
    request1=2, request2=2, request3=2, request4=2

    At second loop stock is 42-8=34.
    And requests are:
    request1=4, request2=4, request3=4, request4=4

    At third loop stock is 34-8=26.

    And requests are:
    request1=6, request2=6, request3=6, request4=6
    (request2 is finished at this step. since it is completed)

    At fourth loop stock is 26-6=20.

    And requests are:
    request1=8, request2=6, request3=8, request4=8

    At fifth loop stock is 20-6=14.

    And requests are:
    request1=10, request2=6, request3=10, request4=10
    (request3 is finished at this step)

    At sixth loop stock is 14-4=10.

    And requests are:
    request1=12, request2=6, request3=10, request4=12

    At 7 loop stock is 10-4=6.

    And requests are:
    request1=14, request2=6, request3=10, request4=14

    At 8 loop stock is 6-4=2.

    And requests are:
    request1=16, request2=6, request3=10, request4=16

    At 9 loop stock is 2-2=0. (it would be 2-4=-2 and since stock is not negative it only covers first request)

    And requests are:
    request1=18, request2=6, request3=10, request4=16

    Since there is no stock, the loop is finished.
    If there would be enough stock, the loop would be finished when all requests would be covered.

    Tuesday, June 7, 2016 3:05 PM