Updating With Aggregats

Question

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)

Answer 1

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

Answer 2

;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 @Jingyang Li was referring to.

Tom