question

InigoMontoya-1790 avatar image
0 Votes"
InigoMontoya-1790 asked TomCooper-6989 answered

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

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

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

0 Votes 0 ·

What do you mean?

0 Votes 0 ·
JingyangLi avatar image JingyangLi InigoMontoya-1790 ·

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

0 Votes 0 ·
Show more comments
JingyangLi avatar image
0 Votes"
JingyangLi answered
 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

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

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered
 ;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

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