Updating With Aggregats

Question

question

InigoMontoya-1790 asked May 5, '22 TomCooper-6989 answered May 5, '22

Updating With Aggregats

i'm importing an excel file into sql server with line item information in it. I need to check if we have enough inventory to fulfill the orders. How can I write a query that will update canProcess = False for each sku that does not have enough inventory?

Create Table OrderInfo
(
   sku varchar(100)
  ,qtyOrdered int
  ,canProcess bit
)

Create Table AvailableQty
(
   sku varchar(100)
  ,qtyAvaliable int
)

Insert Into OrderInfo (sku, qtyordered) Values 
('abc-123', 10),('abc-123', 50),('abc-123', 100), ('def-123', 1000), ('def-123', 100),
('def-123', 100)
Insert Into AvaliableQty Values
('abc-123', 50), ('def-123', 10000)

sql-server-transact-sql

· 4

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JingyangLi · May 05 at 02:44 PM

You need a column to track your item sequence in your table design.

0 ·

InigoMontoya-1790 JingyangLi · May 05 at 02:45 PM

What do you mean?

0 ·

JingyangLi InigoMontoya-1790 · May 05 at 02:52 PM

Add a primary key to your table and a column you can use to track your sequence of import.

0 ·

Show more comments

Answer 1 · 2022-05-05T14:58:49.527Z

JingyangLi answered May 5, '22

 Create Table OrderInfo
 (
 id int identity(1,1) primary key,
    sku varchar(100)
   ,qtyOrdered int
   ,canProcess bit 
   ,importTime datetime
 )
 Create Table AvailableQty
 (
    sku varchar(100)
   ,qtyAvaliable int 
      
 )
 Insert Into OrderInfo (sku, qtyordered, importTime  ) Values 
 ('abc-123', 10 ,'2022-05-05 08:55:32.737'),('abc-123', 50,'2022-05-05 09:05:32.737'),('abc-123', 100,'2022-05-05 10:55:32.737')
 , ('def-123', 1000,'2022-05-05 06:55:32.737'), ('def-123', 100,'2022-05-05 07:55:32.737'),
 ('def-123', 100,'2022-05-05 09:55:32.737')
 Insert Into AvailableQty Values
 ('abc-123', 50), ('def-123', 10000)
    
     
 ;with cte as 
 (
 select oi.* 
 , Case when qtyOrdered<sum(qtyordered) over(partition by oi.sku  order by importTime ) then 0 else 1 end canProcessNew
  from OrderInfo oi
 join AvailableQty aq on oi.sku= aq.sku
 )
    
 update cte 
 Set canProcess=canProcessNew;
    
  select sku,qtyOrdered,canProcess
     
  from OrderInfo oi
    
    
    
 drop Table OrderInfo,AvailableQty

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-05-05T15:02:08.793Z

TomCooper-6989 answered May 5, '22

 ;With cte As
 (Select o.sku, Sum(o.qtyOrdered) As TotalOrdered 
 From OrderInfo o
 Group By o.sku)
 Update o
 Set canProcess = Case When c.TotalOrdered <= a.qtyAvaliable Then 'True' Else 'False' End
 From OrderInfo o
 Inner Join cte c On o.sku = c.sku
 Inner Join AvailableQty a On c.sku = a.sku;

Note that the above marks every order for a given sku with the same canProcess value. But you probably want to be able to process some orders in the case that you don't have enough inventory to do all the orders. If, for example, if you have an order for 50 and an order for 75, but only 100 in inventory, you can do either one of the orders, but not both. The code above sets both orders to CanProcess to False. If that's what you want fine. But if you want to process one of the orders, but not the other, then you need to have a mechanism to choose which order(s) to process. That is what @JingyangLi was referring to.

Tom

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question