SQLTeam.com | Weblogs | Forums

Calculate the amount of picks

HI

I need to calculate the amount of Picks in a shipment , I have the Ordered Quantity and the carton quantity , so as an example

Sales quantity = 12
Carton Qty = 10
Quantity / Carton = 1.2

I want to get to 1 carton 2 left over so total 3 picks as the user would Pick a carton and 2 x loose

Any ideas Greatly appreciated

HI

I found away around it , unless anyone has a cleaner alternative

floor (Inventory.QuantityRequired /Products_UDF.cartonqty) as FullCartonqty,
	Inventory.QuantityRequired -(floor(Inventory.QuantityRequired /Products_UDF.cartonqty)*Products_UDF.cartonqty) as TotalLoosePicks,
	(Inventory.QuantityRequired -(floor(Inventory.QuantityRequired /Products_UDF.cartonqty)*Products_UDF.cartonqty))+ floor (Inventory.QuantityRequired /Products_UDF.cartonqty)     As TotalPicks

may be

declare @Salesquantity numeric(10,2) = 12
declare @CartonQty numeric(10,2) = 10

select leftover = @Salesquantity % @CartonQty 
, carton =  convert(int,@Salesquantity / @CartonQty )
, picks =  @Salesquantity % @CartonQty  + convert(int,@Salesquantity / @CartonQty )```