Inventory update problem

I am trying to update a master inventory table from an order details table, the query below works fine except when the order details table contains the same code number multiple times. When this occurs the update only updates for the first instance of the code number.

How do I make the update work for all the records not just the unique records?

UPDATE Inventory.Inventory
SET Qty = Inventory.Inventory.Qty - Retail.OrderDetails.Qty FROM Inventory.Inventory INNER JOIN
Retail.OrderDetails ON Inventory.Inventory.Code = Retail.OrderDetails.Code
WHERE (Retail.OrderDetails.Invoice = 207070202)

Thanks

Quote:

Originally Posted by Kliot

I am trying to update a master inventory table from an order details table, the query below works fine except when the order details table contains the same code number multiple times. When this occurs the update only updates for the first instance of the code number.

How do I make the update work for all the records not just the unique records?

UPDATE Inventory.Inventory
SET Qty = Inventory.Inventory.Qty - Retail.OrderDetails.Qty FROM Inventory.Inventory INNER JOIN
Retail.OrderDetails ON Inventory.Inventory.Code = Retail.OrderDetails.Code
WHERE (Retail.OrderDetails.Invoice = 207070202)

Thanks

I think it is not possible in SQL Server. But it will work in MS Access.

You have to fetch record and then update it|||hi

i have gone through ur query, but if possible just send me 1 or two records of each table and tell me exactily what u want|||Here is an example,

Invoice table

Code|||Here is an example,

Invoice table

Code Quantity
DM01 2
LG02 2
DM01 3
QP76 1

The update query will update the Inventory table quantity for DM01 by 2 not 5, the second DM01 is not updated

I can get around this by doing a sum query inside the select but it's not ideal.

UPDATE Inventory.Inventory
set RQty = Inventory.Inventory.RQty - od.Quantity
FROM (SELECT Code, SUM(Quantity) AS Quantity FROM Retail.OrderDetails WHERE Invoice = 207022101
GROUP BY Code) as od WHERE(Inventory.inventory.code = od.code)

Inventory system.. Reads and writes

How does one go about building a highly transactional inventory system where
one goes about searching , adding and decrementing inventory..?
Can one share their high level architectural design ?
I am afraid of intensive blocking . When one goes about buying an item from
an inventory, how do you prevent others from not seeing it or even buying it
?
Would really love to hear how this is implemented ?Hassan,
Check the inventory models here:
http://www.databaseanswers.org/data_models/index.htm for a start.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
> How does one go about building a highly transactional inventory system
> where one goes about searching , adding and decrementing inventory..?
> Can one share their high level architectural design ?
> I am afraid of intensive blocking . When one goes about buying an item
> from an inventory, how do you prevent others from not seeing it or even
> buying it ?
> Would really love to hear how this is implemented ?
>
>
>|||Well I guess I was looking for scalability and concurrency issues
surrounding that. With everyone hitting the same one or 2 tables, how can i
ensure there is no blocking along with the fact that I can have a 1000 +
users concurrently viewing the inventory while inventory is being
decremented when a customer buys the item and inventory is incremented when
more items are reordered. And make sure no 2 people also book the same 1
item say as an example thats available..
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23ZOTrjxOGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hassan,
> Check the inventory models here:
> http://www.databaseanswers.org/data_models/index.htm for a start.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
>

Inventory system.. Reads and writes

How does one go about building a highly transactional inventory system where
one goes about searching , adding and decrementing inventory..?
Can one share their high level architectural design ?
I am afraid of intensive blocking . When one goes about buying an item from
an inventory, how do you prevent others from not seeing it or even buying it
?
Would really love to hear how this is implemented ?Hassan,
Check the inventory models here:
http://www.databaseanswers.org/data_models/index.htm for a start.
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
> How does one go about building a highly transactional inventory system
> where one goes about searching , adding and decrementing inventory..?
> Can one share their high level architectural design ?
> I am afraid of intensive blocking . When one goes about buying an item
> from an inventory, how do you prevent others from not seeing it or even
> buying it ?
> Would really love to hear how this is implemented ?
>
>
>|||Well I guess I was looking for scalability and concurrency issues
surrounding that. With everyone hitting the same one or 2 tables, how can i
ensure there is no blocking along with the fact that I can have a 1000 +
users concurrently viewing the inventory while inventory is being
decremented when a customer buys the item and inventory is incremented when
more items are reordered. And make sure no 2 people also book the same 1
item say as an example thats available..
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23ZOTrjxOGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hassan,
> Check the inventory models here:
> http://www.databaseanswers.org/data_models/index.htm for a start.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
>> How does one go about building a highly transactional inventory system
>> where one goes about searching , adding and decrementing inventory..?
>> Can one share their high level architectural design ?
>> I am afraid of intensive blocking . When one goes about buying an item
>> from an inventory, how do you prevent others from not seeing it or even
>> buying it ?
>> Would really love to hear how this is implemented ?
>>
>>
>

Inventory system.. Reads and writes

How does one go about building a highly transactional inventory system where
one goes about searching , adding and decrementing inventory..?
Can one share their high level architectural design ?
I am afraid of intensive blocking . When one goes about buying an item from
an inventory, how do you prevent others from not seeing it or even buying it
?
Would really love to hear how this is implemented ?
Hassan,
Check the inventory models here:
http://www.databaseanswers.org/data_models/index.htm for a start.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
> How does one go about building a highly transactional inventory system
> where one goes about searching , adding and decrementing inventory..?
> Can one share their high level architectural design ?
> I am afraid of intensive blocking . When one goes about buying an item
> from an inventory, how do you prevent others from not seeing it or even
> buying it ?
> Would really love to hear how this is implemented ?
>
>
>
|||Well I guess I was looking for scalability and concurrency issues
surrounding that. With everyone hitting the same one or 2 tables, how can i
ensure there is no blocking along with the fact that I can have a 1000 +
users concurrently viewing the inventory while inventory is being
decremented when a customer buys the item and inventory is incremented when
more items are reordered. And make sure no 2 people also book the same 1
item say as an example thats available..
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:%23ZOTrjxOGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hassan,
> Check the inventory models here:
> http://www.databaseanswers.org/data_models/index.htm for a start.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%234ZWLrqOGHA.1088@.tk2msftngp13.phx.gbl...
>
sql

Inventory Stock, Triggers vs Views/SP

Hello..

I am designing a Database Application that covers Inventory System. And I am now in a dilemma of chosing which design to track Inventory stock better, in performance, reliability, and error free?

1st Design

PRODUCT TABLE
ItemID
ItemName
Price
QtyOnHand
..and other unique info of the product..

SALES TABLE
SalesID
Date
...etc...

SALESDETAIL TABLE
SalesID
ItemID
QtySold
Price

PURCHASE TABLE
PurchaseID
Date
...etc...

PURCHASEDETAIL TABLE
PurchaseID
ItemID
QtyPurchase
Price
...etc...

and similar design with SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL

Tracking Inventory stock is done by using (update, insert and delete) triggers in each of the DETAILS to update the QtyOnHand in the PRODUCT TABLE

2nd Design

PRODUCT TABLE
ItemID
ItemName
Price
...etc...

INVENTORY TABLE
ItemID
QtyBegin
...etc...

SALES TABLE
SalesID
Date
...etc...

SALESDETAIL TABLE
SalesID
ItemID
QtySold
Price
...etc...

and similar design with PURCHASE+DETAIL, SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL

The later design does not hold QtyOnHand, but only save QtyBegin instead. To get the QtyOnHand, it uses views/stored procedure with Union Query, so it looks like this:

QtyOnHand = QtyBegin + Sum(QtySold) + Sum(QtyPurchase) + Sum(QtySalesReturn) + ......

And at the end of a accounting period, the calculation of the QtyOnHand will be the QtyBegin of the next accounting period.

According to you guys, which way is better in PERFORMANCE, RELIABILITY, ERROR FREE, and why? What are the pros and cons of these two?

Thanks a lot.Hi

I would suggest to have design 2 as my option. Performance without reliability is of no use. There is no meaning in giving a wrong information to the user.

In the first design, you are storing the stock of each item whenever there is a movement of the product, either inward or outward. As per your design, let us assume, initially you are having a product, Product A, with no stock. Now you make a purchase on 01/23/2007 for quantity 50. Now the QtyOnHand will have value 50. Suppose you make a sales on 01/25/2007 for 30, as per your design you will update the QtyonHand field, which will now become 20. Now, if I need to get the stock of Product A on 01/04/2007, I cannot use the value in the field QtyonHand, which will be wrong. In this case it will be 20. But actually it must be 50. So we need to calculate the stock by adding the inwards and deducting the outwards. Also if you allow backdate billing, stock updation will become a serious issue.

In the second design, I accept that the performance will be lesser than the first one. But by careful designing that can also be solved. For eg, having a seperate table which contains a date field, Product key and QtyonHand fields. For each purchase, sales and other product movement transactions, you can update this table for each day. There can be several other methods as well. If you come across please intimate me as well.

with regards

Abdur Raoof M|||For what it's worth...
No matter which solution you choose, don't forget to leave room to account for stock transactions that are not
related to sales and receipts, such as damage or shrink.
Invariably, Beginning inventory-sales+purchases<>actual ending inventory.|||@.Abdul Raoof

I very much agree that the 2nd one is far more reliable, but I am still wondering how much more do I have to pay for the reliability? I have done the 2nd one before, and the difference to load the 'item' table without calculating qty compared to load a view that calculates the QtyOnHand with many tables related (although there are only 2 tables with more than 30000 rows) is about 1 sec.

And thanks for minding me the problem of the 1st design, never thought of that before.

@.RedNeckGeek
I think I have included Adjustment Table if that's what you mean?
Beginning inventory-sales+purchases-adjustment = actual ending inventory :)

Thank you Guys.

Anyone? Please... feel free to comment...

Inventory SQL server?

Is there any way I can correctly identify all instances of SQL server
in my environment? We are using SMS 2003 for inventory.
I need to be able to differentiate between an actual SQL server
serving a DB, a workstation just running admin tools, an MSDE
installation, or a SQL Express installation.
Unfortunately, sqlservr.exe appears to be installed for all these
instances so I can't use that as a flag file. Add Remove programs is
another option, and that appears to identify The SQL Express version,
but does not appear to differentiate between the Server, the admin
tools install, or MSDE. The same goes for the service name.
The purpose of this is to reconcile our license counts in case of
audits. You'd think a large corporation could keep better track of
this stuff, but its kind of the wild west out here, and we're just
starting with putting up the barbed wire. I've worked a couple of
large organizations and haven't found one yet that does a good job of
this.
This it will have to something that can automated to process 100s or
even thousands of servers and workstations in our enterprise,
preferably using SMS to gather the data in some form. If necessary
I could use powershell , WMI or some vbscript, but I need something
to key on.
> Is there any way I can correctly identify all instances of SQL server
> in my environment?
There is no 100% reliable way. Most methods use the same technique to
"poll" workstations / servers in the network to check if SQL Server is
running. Unfortunately, various factors can inhibit the ability to do so...
port 1434 is closed (on individual machines, or network-wide via firewall),
some instances may be marked as hidden, some instances may not respond in
time, etc. etc.
Assuming none of these will actually be serving 24x7 production services, a
surefire way to figure out if a running SQL Server process is required by
users in your network is to take the service offline for 24 hours (or until
someone complains). :-)
Sorry I don't have a more foolproof automated way, but sadly, no such thing
exists.
A
|||Quest has a free tool called Quest Discovery Wizard.
There is another tool called SqlRecon, slow but works.
http://www.specialopssecurity.com/labs/sqlrecon/
Nothing I know will tell the difference between MSDE and regular SQL.
If you know a good VB programmer with WMI knowledge that might help!
The Quest tool will at least give you a base line of what machines to
hit.

Inventory SQL server?

Is there any way I can correctly identify all instances of SQL server
in my environment? We are using SMS 2003 for inventory.
I need to be able to differentiate between an actual SQL server
serving a DB, a workstation just running admin tools, an MSDE
installation, or a SQL Express installation.
Unfortunately, sqlservr.exe appears to be installed for all these
instances so I can't use that as a flag file. Add Remove programs is
another option, and that appears to identify The SQL Express version,
but does not appear to differentiate between the Server, the admin
tools install, or MSDE. The same goes for the service name.
The purpose of this is to reconcile our license counts in case of
audits. You'd think a large corporation could keep better track of
this stuff, but its kind of the wild west out here, and we're just
starting with putting up the barbed wire. I've worked a couple of
large organizations and haven't found one yet that does a good job of
this.
This it will have to something that can automated to process 100s or
even thousands of servers and workstations in our enterprise,
preferably using SMS to gather the data in some form. If necessary
I could use powershell , WMI or some vbscript, but I need something
to key on.> Is there any way I can correctly identify all instances of SQL server
> in my environment?
There is no 100% reliable way. Most methods use the same technique to
"poll" workstations / servers in the network to check if SQL Server is
running. Unfortunately, various factors can inhibit the ability to do so...
port 1434 is closed (on individual machines, or network-wide via firewall),
some instances may be marked as hidden, some instances may not respond in
time, etc. etc.
Assuming none of these will actually be serving 24x7 production services, a
surefire way to figure out if a running SQL Server process is required by
users in your network is to take the service offline for 24 hours (or until
someone complains). :-)
Sorry I don't have a more foolproof automated way, but sadly, no such thing
exists.
A|||Quest has a free tool called Quest Discovery Wizard.
There is another tool called SqlRecon, slow but works.
http://www.specialopssecurity.com/labs/sqlrecon/
Nothing I know will tell the difference between MSDE and regular SQL.
If you know a good VB programmer with WMI knowledge that might help!
The Quest tool will at least give you a base line of what machines to
hit.

inventory FIFO

Hi,
I want to calculate inventory with FIFO METHOD. my tabel is :
inventory(doc char(10),tgl date, qty numeric(15,2), price
numeric(15,2))
doc tgl PRICE QTY
---
FP123 02/02/06 180 10
ASD12 07/02/06 -9
FP23 10/02/06 150 2
ASD14 11/02/06 -2
I want to result is as below:
doc tgl price qty
---
FP123 02/02/06 180 10
ASD12 07/02/06 180 -9
FP23 10/02/06 150 2
ASD14 11/02/06 180 -1
ASD14 11/02/06 150 -1
how to create sintak SQL SERVER ?
thank you for your advanced.
best regards,
alimIf I understand your problem correctly , the sybtax is;
SELECT doc,tgl,qty,price ORDER BY tgl ASC
Jack Vamvas
___________________________________
Receive free SQL tips - http://www.ciquery.com/sqlserver.htm
"alim" <alfen_lim@.yahoo.com> wrote in message
news:1141293436.115783.286230@.i40g2000cwc.googlegroups.com...
> Hi,
> I want to calculate inventory with FIFO METHOD. my tabel is :
> inventory(doc char(10),tgl date, qty numeric(15,2), price
> numeric(15,2))
> doc tgl PRICE QTY
> ---
> FP123 02/02/06 180 10
> ASD12 07/02/06 -9
> FP23 10/02/06 150 2
> ASD14 11/02/06 -2
> I want to result is as below:
> doc tgl price qty
> ---
> FP123 02/02/06 180 10
> ASD12 07/02/06 180 -9
> FP23 10/02/06 150 2
> ASD14 11/02/06 180 -1
> ASD14 11/02/06 150 -1
> how to create sintak SQL SERVER ?
> thank you for your advanced.
> best regards,
> alim
>|||hi jack,
cannot used syntax like :SELECT doc,tgl,qty,price ORDER BY tgl ASC
because
in tabel : ASD14 11/02/06 -2
I want to :
ASD14 11/02/06 180 -1
ASD14 11/02/06 150 -1
best regards,
alim|||Have you been over to www.dbazine.com and looked for my article on
inventory?

Inventory Cube

How have other tackled the Inventory Cube? I am thinking
along the lines of
these two strategies
1. 1 and only one snap shot at a time that we decide
accurately reflects
realistic inventory levels. Meaning not necessarily at
the end of the month
when they could be low.
or
2. Take 4 snapshots a month and average those 4 together
giving me my
inventory snap shot for the month. Does anyone have other
ideas? I'm
interested in hearing how you have solved this interesting
situation.
-mikeHi Michael,
I noticed that the issue was posted in the following groups
microsoft.public.sqlserver.datawarehouse and
microsoft.public.sqlserver.olap. We have added a reply to you at
http://support.microsoft.com/newsgr...&NewsGroup=micr
osoft.public.sqlserver.olap
If you have follow up questions, please post there and we will work with
you. Thanks.
Regards.
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.sql