Moving Items around in storage.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Moving Items around in storage.

Author

Topic

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2003-10-21 : 15:03:09

I’ve got several locations to track items in storage before they leave our place. I also need to know the transfer number (paper work used to transfer items in, out and around) used to move the items around.

When an item is generated it starts at one location (20 items at A), but then can be moved to several different locations (10 items to B storage, 10 items to C storage) each with their own transfer number. Then on top of that, items in C storage can even be broken apart more at a later date (5 items to D storage). Finally, items get removed when they are transferred offsite (transfer number again).

I need to be able to query the items and tell me how many items are where and what transfer number got them there.

I am having a real tough time with the design of this database. I have an ITEMS, TRANSACTION AND LOCATIONS table, but some how keep coming up with wrong answers. Example: I can get the correct QTY and LOCATIONS, but transfer number is wrong.

Any design help would be great!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-21 : 16:00:47

DDL and DML would help us. DDL is CREATE TABLE statements and DML would be sample data with INSERT INTO statements. The select statement that you are working on should be provided as well.

Tara

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-10-21 : 16:33:15

could it be that your items table has a quantity column, if it does then it isn't an items table it is an orders table or a lots table etc.

20 items at A could be an order A1, (got to start somewhere, usually a document is the starting point rather than the actual "things" like a work order that says "here hold my package for me"
an order item would be 001 = 10 items to B storage
another order item would be 002 = 10 items to C storage
then each order item could have subitems
an order subitem could be sub001-1 = 5 items to D storage

A1
001
sub001-1
002

Voted best SQL forum nickname...."Tutorial-D"

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2003-10-21 : 17:29:52

ITEM
i_num - PK
i_earliestdate (earliest accumulation date)
i_genprocess (process that made item)
i_genlocation (where it was made)

TRANSACTION
t_id - PK
i_num (FK – ITEM)
t_pickuplocation (pickuplocation)
t_storagelocation (location of storage)
t_numitems (number of items)
t_entrydate (timestamp)
t_begintransdate (date storage begins and transfer actually happened)
t_transactiontype (values: ADD, REMOVE)
t_manifestnum (transfer number)

This was my thinking….
In order for an item to even be moved it has to be generated first. So I create some type of “open item pool” to pick from.

INSERT ITEM VALUES (1, ‘1/1/03’,’AMMO PROCESS’, ‘A-AREA’)
INSERT TRANSACTION (1,1,’A-AREA’,NULL,100,’1/1/03’, ’1/1/03’,’ADD’,NULL)

Get open items and display them for the users to mess with (genlocation and pickuplocation will be equal only once for this item)..
SELECT JOINS, OTHER CRAP, WHERE I_GENLOCATION = T_PICKUPLOCATION AND SUM(T_NUMITEMS) > 0

Now that I got something in there. I want to move it to ‘B-AREA’ and still retain my history. This will be the first actual movement of the items.
This will move it from A-AREA and add it to B-AREA.
INSERT TRANSACTION (2,1,’A-AREA’,’B-AREA’,-100,’1/1/03’, ‘1/1/03’,’REMOVE’,’1234’)
INSERT TRANSACTION (3,1,’B-AREA’,NULL,100,’1/1/03’, ’1/1/03’,’ADD’,NULL)

Show me where items are stored.
SELECT JOINS, OTHER CRAP SUM(T_NUMITEMS) AND GROUP BY T_PICKUPLOCATION WHERE T_NUMITEMS > 0 AND GENLOCATION <> T_PICKUPLOCATION
This will give me a result of:
100 items at B-AREA.

I can even break them apart and move half of them to C-AREA using the above design\query..HOWEVER….
I am having a terrible time trying to figure out which manifest got them where they are at, and how much of them are still there.

Example: We went ahead and moved 50 of them to C-AREA. I do this by REMOVE(ing) -50 of them from B-AREA (generates manifest – number 5678) and then adding a transaction to ADD 50 to C-AREA (no manifest on this).

My answer should be

50 in B-AREA, Manifest number 1234 (even though 100 came over on this one)
50 in C-AREA, Manifest number 5678 (only 50 came over on this).

None of this is set in stone. Open to all ideas and options.

Thanks for taking the time to read my babble.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources