asked on

Loop update table records

I am working on a project where I have to find one or several empty compartments in my shelving to store the quantity of one SKU.

I need to loop update the rows where QTY is equal to 0 until I complete the whole quantity and I don’t know how to write this looping function in SQL.

Suppose that I am receiving an order with a quantity of 100 of “ABCD” items to store.

Assuming that my SKU “ABCD” represents a volume of 5 compartments (each compartment can only store 20 tiems); I will need to loop trough my entire shelving compartment table to find 5 empty compartments and assign in the SKUnb column the “ABCD” reference and place 20 PCS in the “QTY” column until I complete the 100 PCS. At the same time I flag the “IsInUse” column with a yes.

My Compart table

ShelveID  ShelvePosition   SKUnb   Qty   isInUse
1              A001	XYZ       20  yes
2              A002        XYZ       20    yes
3              A003        null       0      no
….           ….          ……    	……  ……
100          X001        null   	0      no
101          X002        null     0      no

Open in new window

If for instance the quantity is 110 there will be 5 Shelve position filled with 20 and one with 10. Total 6 empty rows updated.

When I need to retrieve some quantity I will inverse the process and flag the the IsInUse with “no” if the compartment is empty.

Thanks for helping.

JoeNuvo

here is sample stored procedure for "Store" process.

for retrive process, I still can't get idea yet.

CREATE PROCEDURE Store
	@SKU varchar(10),
	@ItemCount int
AS
Declare @RequiredCompartment int, @LastCompartment int, @ShelfCount int
-- calculate total compartment to stored
SET @RequiredCompartment = CEILING (@ItemCount / 20.00)  
-- calculate amount to store in last compartment
SET @LastCompartment = @ItemCount - (@RequiredCompartment - 1) * 20
-- preset value
SET @ShelfCount = 0

UPDATE TOP (@RequiredCompartment) [Compart]
SET
	@ShelfCount = @ShelfCount + 1,
	SKUnb = @SKU,
	Qty = CASE @ShelfCount WHEN @RequiredCompartment THEN @LastCompartment ELSE 20 END,
	isInUse = 'yes'
FROM [Compart]
WHERE isInUse = 'no'

Open in new window

Lowfatspread

so you don't want to add to partially filled comartments?

shouldn't there be a date/time on the compartment so tha retrieval can be
done on an oldest first basis?
declare nslots integer
set @nslots = @noofitems/20 + case when @noofitems % 20 > 0 then 1 else 0 end

update compartment
set isinuse='yes'
,skunb=@sku
,qty=case when z.rn < @nslots then 20 else @noofitems - (@nslots*20) end
from compartments as c
Inner Join (select row_number() over (order by shelve_id) as rn
shelve_id
from compartment
where qty=0 and isinuse='no'
) as z
on c.shelve_id=z.shelve_id
where z.rn between 1 and @nslots

pascalmartin

ASKER

Hi JoeNuvo,

Thanks, I got the logic, but I didn't specify that I am using SQL server 2000 and I don't understand this line:
UPDATE TOP (@RequiredCompartment) [Compart]
Can you explain? Or change it to SQL server 2000 syntax.

Thanks!

pascalmartin

ASKER

Hi Lowfatspread,

Same as above, I am on SQL server 2000 and the function "Row_number()" is not working.

Thanks!

Lowfatspread

1) if its not sql 2005 then just use the sql server zone.... and state the version/edition at the start of the question.

Lowfatspread

update compartment
  set isinuse='yes'
    ,skunb=@sku
   ,qty=case  when z.rn < @nslots then 20 else @noofitems - (@nslots*20) end
from compartments as c
Inner Join (select count(*) as rn,
                         shelve_id
                    from compartment as c1
                    Inner join compartment as c2
                      on c1.shelve_id>=c2.shelve_id
                     and c1.qty=c2.qty
                     and c1.isinuse=c2.isinuse
                   where c1.qty=0 and c1.isinuse='no'
               ) as z
 on c.shelve_id=z.shelve_id
where z.rn between 1 and @nslots

Open in new window

JoeNuvo

I've question to ask

let say, on shelf already got some of SKU “ABCD” with the last one filled at 10 (out of 20)
when new item of SKU “ABCD” add into shelf, are we going to fill the leftover space of above compartment?

if answer is yes, then I will give some code to try for both store and retrieve process.

pascalmartin

ASKER

Hi JoeNuvo,

Yes, it would be right to assign the same SKU number in the left over space in the shelve.
Then when I pull quantities out of the shelves I need to update the storage space accordingly with another procedure.

Thanks!

pascalmartin

ASKER

Hi JoeNuvo,

Actually, the code return the following errors: Ambiguous column name 'shelve_id'.
If I change to: Inner Join (select count(*) as rn,
c1.shelve_id
from compartments as c1

I got another error: Column 'c1.shelve_id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Lowfatspread

update compartment
  set isinuse='yes'
    ,skunb=@sku
   ,qty=case  when z.rn < @nslots then 20 else @noofitems - (@nslots*20) end
from compartments as c
Inner Join (select count(*) as rn,
                         c1.shelve_id
                    from compartment as c1
                    Inner join compartment as c2
                      on c1.shelve_id>=c2.shelve_id
                     and c1.qty=c2.qty
                     and c1.isinuse=c2.isinuse
                   where c1.qty=0 and c1.isinuse='no'
                  group by c1.shelve_id
               ) as z
 on c.shelve_id=z.shelve_id
where z.rn between 1 and @nslots

Open in new window

ASKER CERTIFIED SOLUTION

JoeNuvo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pascalmartin

ASKER

Hi JoeNuvo,
I am on the "Store" procedure and tried it with and without the FirstFillOn case.
Without, It work and I can get the expected result.
With I receive the following error msg:
"Disallowed implicit conversion from data type datetime to data type timestamp, table 'WMS.dbo.Compartment', column 'FirstFillOn'. Use the CONVERT function to run this query"
I need to have this one fixed to try the proc to retreive the qty.
Thanks for the help you are providing..

JoeNuvo

I'm forget to fix some place when I'm comment out for FirstFillOn

for Retrieve
Code line 16 : remove comma (or move it to in front of "FirstFillOn" line 17)
Code line 32 : comment out this line
Code line 45 : remove comma (or move it to in front of "FirstFillOn" line 17)

JoeNuvo

and.. FirstFillOn data type is datetime, not timestamp

pascalmartin

ASKER

hI, it looks awsome, just one thing that does, go right is your comment:
Code line 32: comment out this line. What do you mean? how to change the hyphens? I tried several ways but can't get it right.
+ 'ORDER BY FirstFillOn'

JoeNuvo

if you don't have field FirstFillOn
you have to change code line 32 to be as below
(or it will error)

-- + ' ORDER BY FirstFillOn'

Open in new window

but since you have it, you can just ignore it.

pascalmartin

ASKER

Great solution, I wish I can connect again with JoeNuvo, I will have other questions..