Solved

Loop update table records

Posted on 2011-02-13
17
451 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:pascalmartin
  • 7
  • 6
  • 4
17 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 34882206
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

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34886677
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
0
 

Author Comment

by:pascalmartin
ID: 34896936
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!
0
 

Author Comment

by:pascalmartin
ID: 34896998
Hi Lowfatspread,

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

Thanks!
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34897673
1) if its not sql 2005 then just use the sql server zone.... and state the version/edition at the start of the question.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34897746

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

0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 34903803
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.
0
 

Author Comment

by:pascalmartin
ID: 34907503
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!
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:pascalmartin
ID: 34907783
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.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34909783

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

0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 500 total points
ID: 34913266
Here is my code to perform store and retrieve item from compartment

Note
1. when Store, will try to fill the leftover space first
    But if number of new items is able to fill full compartment (20, 40 , ...) it will fill the blank compartment only
2. when retrieve, will first get from compartment which not full
4. I have put code to keep date/time of first time item fill into each compartment in field name FirstFillOn
    you can remove the comment --:: in order to utilize this

ok.. here is the code

CREATE PROCEDURE Store
	@SKU varchar(10),
	@ItemsCount int
AS
SET NOCOUNT ON
-- Step 1 : get list of compartment
Declare @RequiredCompartment int, @LastCompartmentAmount int, @NoMod int, @OldQty int
SET @RequiredCompartment = @ItemsCount / 20
SET @NoMod = 0
IF (@ItemsCount % 20) > 0
BEGIN
	SET @RequiredCompartment = @RequiredCompartment + 2
	SET @NoMod = 1
END

Create TABLE #TargetCompartment (id int IDENTITY (1,1), ShelveID int PRIMARY KEY, OldQty int)
Declare @sql nvarchar(1000)
SET @sql = 'INSERT INTO #TargetCompartment (ShelveID, OldQty) SELECT TOP ' 
+ CAST(@RequiredCompartment as nvarchar(10)) 
+ ' ShelveID, Qty FROM compartment WHERE isInUse = ''no'''
+ CASE @NoMod WHEN 1 THEN ' OR (SKUnb = ''' + REPLACE(@SKU,CHAR(39),CHAR(39) + CHAR(39)) + ''' AND Qty < 20)' ELSE '' END
+ ' ORDER BY Qty DESC, ShelveID'

EXECUTE (@sql)

-- Step 2 : remove exceed compartment
SELECT @OldQty = OldQty FROM #TargetCompartment WHERE OldQty > 0
IF (@RequiredCompartment * 20) - (@ItemsCount + ISNULL(@OldQty, 0)) >= 20
BEGIN
	DELETE FROM #TargetCompartment
	WHERE ShelveID = (SELECT TOP 1 ShelveID FROM #TargetCompartment ORDER BY id DESC)

	SET @RequiredCompartment = @RequiredCompartment - 1
END
SELECT @LastCompartmentAmount = @ItemsCount + ISNULL(@OldQty,0) - ((@RequiredCompartment-1) * 20)

-- Step 3 : put items into compartment
UPDATE compartment
SET
	isInUse = 'yes',
	SKUnb = @SKU,
	Qty = CASE id
		WHEN @RequiredCompartment THEN @LastCompartmentAmount
		ELSE 20 END
	--::,FirstFillOn = CASE WHEN OldQty > 0 THEN FirstFillOn ELSE CURRENT_TIMESTAMP END
FROM #TargetCompartment T
WHERE compartment.ShelveID = T.ShelveID

-- Step 4 : report
SELECT c.ShelveID, c.ShelvePosition, T.OldQty CurrentQty, (c.Qty - T.OldQty) as QtyToAdd
FROM compartment c INNER JOIN #TargetCompartment T ON c.ShelveID = T.ShelveID

DROP TABLE #TargetCompartment

GO

Open in new window


CREATE PROCEDURE Retrieve
	@SKU varchar(10),
	@ItemsCount int
AS
SET NOCOUNT ON
-- Step 1 : Removed from partial fill first.
Declare @ShelveID int, @OldQty int, @NewQty int
SELECT @ShelveID = ShelveID, @OldQty = Qty
FROM compartment WHERE isInUse = 'yes' AND SKUnb = @SKU AND Qty < 20

IF @@ROWCOUNT > 0
BEGIN
	UPDATE compartment
	SET @NewQty = Qty = @OldQty - CASE WHEN @ItemsCount > @OldQty THEN @OldQty ELSE @ItemsCount END,
		SKUnb = CASE @NewQty WHEN 0 THEN NULL ELSE SKUnb END,
		isInUse = CASE @NewQty WHEN 0 THEN 'no' ELSE 'yes' END,
		--::FirstFillOn = CASE @NewQty WHEN 0 THEN NULL ELSE FirstFillOn END
	WHERE ShelveID = @ShelveID
	SET @ItemsCount = @ItemsCount - (@OldQty - @NewQty)
END

-- Step 2 : Get list of compartment to remove
Declare @RequiredCompartment int, @LastCompartmentAmount int
SET @RequiredCompartment = (@ItemsCount / 20) + CASE WHEN (@ItemsCount % 20) > 0 THEN 1 ELSE 0 END

Create TABLE #TargetCompartment (id int IDENTITY (1,1), ShelveID int PRIMARY KEY, OldQty int)
Declare @sql nvarchar(1000)
SET @sql = 'INSERT INTO #TargetCompartment (ShelveID, OldQty) SELECT TOP ' 
+ CAST(@RequiredCompartment as nvarchar(10)) 
+ ' ShelveID, Qty FROM compartment WHERE isInUse = ''yes'' AND SKUnb = '''
+ REPLACE(@SKU,CHAR(39),CHAR(39) + CHAR(39)) + ''''
+ ' ORDER BY FirstFillOn' 

EXECUTE (@sql)

-- Step 3 : remove items from compartment
SELECT @LastCompartmentAmount = (@RequiredCompartment * 20) - @ItemsCount

UPDATE compartment
SET
	@NewQty = Qty = CASE id
		WHEN @RequiredCompartment THEN @LastCompartmentAmount
		ELSE 0 END,
	SKUnb = CASE @NewQty WHEN 0 THEN NULL ELSE SKUnb END,
	isInUse = CASE @NewQty WHEN 0 THEN 'no' ELSE 'yes' END,
	--::FirstFillOn = CASE @NewQty WHEN 0 THEN NULL ELSE FirstFillOn END
FROM #TargetCompartment T
WHERE compartment.ShelveID = T.ShelveID

-- Step 4 : report
INSERT INTO #TargetCompartment (ShelveID, OldQty) VALUES (@ShelveID, @OldQty)
SELECT c.ShelveID, c.ShelvePosition, T.OldQty CurrentQty, (T.OldQty - c.Qty) as QtyToRemove
FROM compartment c INNER JOIN #TargetCompartment T ON c.ShelveID = T.ShelveID

DROP TABLE #TargetCompartment

GO

Open in new window

0
 

Author Comment

by:pascalmartin
ID: 34916872
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..

0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 34918978
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)
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 34919075
and.. FirstFillOn data type is datetime, not timestamp
0
 

Author Comment

by:pascalmartin
ID: 34927771
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'
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 34930456
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.
0
 

Author Closing Comment

by:pascalmartin
ID: 34931325
Great solution, I wish I can connect again with JoeNuvo, I will have other questions..
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to set audit trial on sql server 4 36
Sql query 12 65
Upgrading SQL 2005 Express to 2008 R2 Express 31 47
Grid querry results 41 54
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now