Inventory Query - SQL Server Forums

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)

Inventory Query

Author

Topic

steve_c
Yak Posting Veteran

54 Posts

Posted - 2007-08-28 : 05:44:18

Hi all - am having trouble designing a query for stock control.

Have designed a stock/despatch/accounts system. I need a query to give me the amount of stock on hand for a particular products. I also have a stock take table to keep the cost of the query down to a minumum (will have stock takes every few months or so)

Tables are as follows (cut down for ease):

Products
productid

Orders
orderid
orderdate

OrderLine
orderid
orderlineid
productid
qty

Despatch
despatchid
despatchdate

DespatchLine
despatchid
despatchlineid
productid
qty

PurcaseOrder
purchaseorderid
purcaseorderdate

PurchaseOrderLine
purchaseorderid
purchaseorderlineid
productid
qty

Receipt
receiptid
receiptdate

ReceiptLine
receiptlineid
receiptid
productid
qty

StockTake
stocktakeid
stocktakedate
productid
qty

I basically want a query which works out the stock of a product as such:

stock = (qty ordered) - (qty received) where orderdate > stocktakedate and purchaseorderdate > stocktakedate

Am grateful for any help.

Thanks in advance.

Steve.

Kristen
Test

22859 Posts

Posted - 2007-08-28 : 10:14:49

If you use a Query to get the Stock Level, based on transactions in receipts, Dispatched and StockTake Adjustments tables, it will get progressively slower as the number of transactions in those tables grows.

You might want to consider adding a QuantityOnHand column to your Products table, or a new StockLevel table, and some sort of Trigger-type code that keeps the figure up to date.

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77924

Kristen

steve_c
Yak Posting Veteran

54 Posts

Posted - 2007-08-28 : 10:43:01

Ahh right. I remember when I started using Access years ago someone warned me to always calculate stock by using a query such as the one I outlined in my first post and to minimize the hit on the processor by performing regular stock takes...

I am fairly new to SQL and so I am unfamiliar with triggers...

Will it take me long to work out? Any helpful pointers?

Thanks!

Kristen
Test

22859 Posts

Posted - 2007-08-29 : 08:28:58

"Will it take me long to work out?"

Doubt it.

"Any helpful pointers?"

Newbies often fall into the trap of thinking that a Trigger fires once for each ROW being inserted/updated/delete. It doesn't, it fires once per BATCH.

So:

INSERT INTO MyTable
SELECT *
FROM MyOtherTable

will call the trigger once for however many records are in MyOtherTable.

I reckon you need something like (this is UNTESTED!!)


CREATE TRIGGER MyTrigger ON DespatchLine
FOR INSERT, UPDATE, DELETE
AS

UPDATE P
SET P.MyQtyOnHand = P.MyQtyOnHand - COALESCE(I.qty, 0) + COALESCE(D.qty, 0)
FROM inserted AS I
     FULL OUTER JOIN deleted AS D
         ON D.productid = I.productid
     JOIN Products AS P
         ON P.productid = COLAESCE(D.productid, I.productid)

Repeat for ReceiptLine (swapping +/- in the SET P.MyQtyOnHand = ... statement)

And then either the same for StockTake (if it represents a +/- adjustment value [if its real-time rather than "warehouse closed for stocktake"], otherwise just replace the value of MyQtyOnHand with the stock-take value

Kristen

steve_c
Yak Posting Veteran

54 Posts

Posted - 2007-08-29 : 11:28:12

Hi thanks - I have got this working perfect for the receiptline table:

UPDATE P
SET 
P.qtyonhand = P.qtyonhand + COALESCE(I.qty, 0) + COALESCE(D.qty, 0)
FROM inserted AS I
     FULL OUTER JOIN deleted AS D
         ON D.productid = I.productid
     JOIN Products AS P
         ON P.productid = COALESCE(D.productid, I.productid)

END

However, sometimes a receipt line is edited. So instead of say 100 items being received, the user edits and updates the receive line to say 50. So I guess I would need to first subtract the old amount from qtyonhand and then add the updated amount.

But how do I access the value of the qty on the reciptline before it is updated?

Thanks.

Kristen
Test

22859 Posts

Posted - 2007-08-29 : 11:38:47

"I have got this working perfect for the receiptline table"

Well, assuming that I got my original correct! you need:

SET P.MyQtyOnHand = P.MyQtyOnHand + COALESCE(I.qty, 0) - COALESCE(D.qty, 0)

I = Inserted = the new data being saved (will be NULL for a delete)
D = Deleted = the original data (will be NULL for an INSERT)

"However, sometimes a receipt line is edited"

That's OK, and should be taken care of by the code above.

You create a new transaction for 100 items.

INSERTED.Qty = 100, DELETED.Qty = NULL

Thus: SET P.MyQtyOnHand = P.MyQtyOnHand + 100 - 0

Then you edit, and "Update", the row to be 50 items instead.

Now INSERTED.Qty = 50, DELETED.Qty = 100

Thus: SET P.MyQtyOnHand = P.MyQtyOnHand + 50 - 100

Hope that helps,

Kristen

steve_c
Yak Posting Veteran

54 Posts

Posted - 2007-08-29 : 11:40:40

Ahh I think I've got it - is this correct?

UPDATE P
SET 
P.qtyonhand = P.qtyonhand - COALESCE(d.qty, 0) + COALESCE(I.qty, 0)
FROM inserted AS I
     FULL OUTER JOIN deleted AS D
         ON D.productid = I.productid
     JOIN Products AS P
         ON P.productid = COALESCE(D.productid, I.productid)

The "Deleted" was confusing me...

steve_c
Yak Posting Veteran

54 Posts

Posted - 2007-08-29 : 11:41:32

Hi Kristen - sorry you beat me to it. Thanks very much for your help.

Stephen.

Kristen
Test

22859 Posts

Posted - 2007-08-29 : 11:42:13

Crossed with my reply a bit higher up I reckon ??

Kristen
Test

22859 Posts

Posted - 2007-08-29 : 11:42:46

Damn ... "this game can be played by any number of players for any length of time"!!

steve_c
Yak Posting Veteran

54 Posts

Posted - 2007-08-29 : 11:47:20

Yup - had just worked it out! This will make my life a lot easier. Are there any down sides to doing things this way?

Kristen
Test

22859 Posts

Posted - 2007-08-29 : 12:15:47

"Are there any down sides to doing things this way?"

The trigger could be accidentally dropped, or disabled. Your stock-levels would not change. You would struggle to know which needed updating (you could add a column to [Products] called QtyOnHandLastChanged and add to the SET phrase in the UPDATE:

, QtyOnHandLastChanged = GetDate()

that would help you sort out after an accident, and might also be useful, generally, for folk to know when the last stock-movement was on a given product.

Also, the trigger slows the Insert/Update/Delete transaction.

The original transaction on the table happens, THEN the trigger fires, which in turn causes a second statement to execute changing the Products.qtyonhand. However, [personally] I think that is "reasonable" to avoid a hefty query in order to get the current stock level.

You could do the same thing within the business logic that updates the DespatchLine and ReceiptLine tables, however you would have to be CERTAIN to maintain the logic WHEREVER changes to those tables could occur - INCLUDING in any Import-from-external-data type routines. TRIGGERs centralise all that. To my mind that is a reasonable cost to pay for this particular type of "must always happen" calculation.

Might be slightly more efficient (i.e. if the DespatchLine / ReceiptLine transactions are likely to be edited when the QTY field does NOT change to add:

WHERE COALESCE(I.qty, 0) <> COALESCE(D.qty, 0)

which will prevent a physical update taking place when there is an edit that does NOT create, delete or CHANGE the Qty.

Can't think of anything else untoward

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources