Summarize Function in Stored Procedure

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

Transact-SQL (2000)

Summarize Function in Stored Procedure

Author

Topic

ilimax
Posting Yak Master

164 Posts

Posted - 2004-02-05 : 10:52:20

My stored procedure works fin with execution but I have one problem.
If I have two same records in POSInvoiceLineitems table I am updating only first one.
This is example of POSInvoiceLineitems table

InvNum ItemNum Qty
---------------------------
22 10 2
22 20 3
22 10 4

If I send parameter InvNum 22 I am updating ItemNum 10 just with 2 and ItemNum 20 with 3. There is no updating ItemNum 10 with 4.
I understand I need Summirize Data now, but I do not know how I can do that.Please help if you have idea.

CREATE PROCEDURE UpdateInventory
@myinvoice varchar(20)
AS
SET NOCOUNT ON
UPDATE Inventory
SET Inventory.[Quantity in Stock] = Inventory.[Quantity in Stock]-POSInvoiceLineitems.[Quantity]
FROM Inventory
INNER JOIN POSInvoiceLineitems
ON Inventory.[Item Number] = POSInvoiceLineitems.[Item Number]
WHERE POSInvoiceLineitems.[Invoice Number]= @myinvoice

HendersonToo
Starting Member

20 Posts

Posted - 2004-02-05 : 13:21:19

This query will produce a distinct record for each invnum and itemnum combination containing a sum:


SELECT [Invoice Number], [Item Number], SUM([Quantity])
FROM POSInvoiceLineItems
GROUP BY [Invoice Number], [Item Number]
ORDER BY [Invoice Number], [Item Number]

However, that can't be used directly in your UPDATE statement, due to the ORDER BY. You must always order the grouped records, otherwise results will vary. You can put this result into a temporary table - for example SELECT INTO #InvUpdates and then use that as the source for your UPDATE ... FROM #InvUpdates

Perhaps somebody else will recogonize a one-statement trick for this particular case.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-05 : 14:53:28

I don't understand the problem.

Using your example, what should the data look like after the UPDATE runs.

Tara

ilimax
Posting Yak Master

164 Posts

Posted - 2004-02-05 : 16:54:36

QUANTITY in table INVENTORY needs to be updated.
Procedure works fine if I do not have two records for same ITEM NUMBER in POSINVOICELINEITEMS table.If I have 2 records, Procedure update QUANTITY in table INVENTORY only with first one.

Exemple of table POSInvoiceLineitems:
InvNum: ItemNum: Qty:
---------------------------
22: 10 : 2
22: 20 : 3
22: 10 : 4

So, for ItmNum 10 table INVENTORY will be updated just with Qty 2.

ilimax

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-05 : 16:59:05

The UPDATE statement should work for both rows. Could you post the DDL (CREATE TABLE statements) for your two tables? Could you also post the data for both tables (sample data) in the form of INSERT INTO statements?

We need to try this out on our own machines.

Tara

ilimax
Posting Yak Master

164 Posts

Posted - 2004-02-06 : 11:20:02

Here are Create table statementS. I am trying some solutions but I still have same problem.
DOES NOT UPDATE SECOND ROW FOR SAME ITEM NUMBER.

CREATE TABLE Inventory (
[Item Number] nvarchar(50) NOT NULL,
[Quantity in Stock] float(8));

CREATE TABLE POSInvoiceLineitems(
[Invoice Number] nvarchar(20) NOT NULL,
[Item Number] nvarchar(50),
Quantity float(8));

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-06 : 11:42:05

Well...you're update statement is not against that table..you're updating inventory...

EDIT: Also you're predicate is on the invoice number not the Item Number.

Which table are you trying to update?

and are we looking at something like:



CREATE TABLE Inventory (
[Item Number] nvarchar(50) NOT NULL,
[Quantity in Stock] float(8));

CREATE TABLE POSInvoiceLineitems(
[Invoice Number] nvarchar(20) NOT NULL,
[Item Number] nvarchar(50),
Quantity float(8));
GO

INSERT INTO Inventory ([Item Number], [Quantity in Stock])
SELECT '10', 9

INSERT INTO POSInvoiceLineitems ([Invoice Number], [Item Number], [Quantity])
SELECT '22', '10', 2 UNION ALL
SELECT '22', '20', 3 UNION ALL
SELECT '22', '10', 4
GO

CREATE PROCEDURE UpdateInventory
	@myinvoice varchar(20)
AS
BEGIN
SET NOCOUNT ON
DECLARE @RowCount int, @Error int, @rc int

    UPDATE Inventory 
       SET Inventory.[Quantity in Stock] = Inventory.[Quantity in Stock]-POSInvoiceLineitems.[Quantity]
      FROM Inventory
INNER JOIN POSInvoiceLineitems
	ON Inventory.[Item Number] = POSInvoiceLineitems.[Item Number] 
     WHERE POSInvoiceLineitems.[Invoice Number]= @myinvoice

    SELECT @RowCount = @@RowCount, @Error = @@Error, @rc = @@RowCount
	IF @RowCount = 0
		BEGIN
		PRINT 'No Rows Updated'
		SELECT @rc = -1
		END
	IF @Error <> 0
		BEGIN
		PRINT 'An Error Occurred'
		SELECT @rc = -2
		END
SET NOCOUNT OFF
END
RETURN @rc
GO

DECLARE @rc int
EXEC @rc=UpdateInventory '22'

SELECT @rc

SELECT * FROM Inventory

SELECT * FROM POSInvoiceLineitems
GO

DROP PROC UpdateInventory
DROP TABLE Inventory
DROP TABLE POSInvoiceLineitems
GO

Brett

8-)

ilimax
Posting Yak Master

164 Posts

Posted - 2004-02-06 : 11:53:40

This morning I got help from one my friend I have now UPDATE STORED PROCEDURE which summarize all Quntity in table p and Update Quanity in table i with totals.
However thank you very much for your help. I am going to review this last replay X00258. Thanks for your try.

THIS IS MY NEW UPDATE PROCEDURE...

CREATE PROCEDURE UpdateInventory
@myinvoice nvarchar(20)
AS
SET NOCOUNT ON
UPDATE Inventory
SET Inventory.[Quantity in Stock] = Inventory.[Quantity in Stock]-POSInvoiceLineitems.SumQty
FROM Inventory
INNER JOIN
(
SELECT [Item Number], Sum([Quantity]) AS SumQty
FROM POSInvoiceLineitems
WHERE [Invoice Number]=@myinvoice
GROUP BY [Item Number]
)
AS POSInvoiceLineitems
ON Inventory.[Item Number] = POSInvoiceLineitems.[Item Number]

Thanks,

Ilimax

HendersonToo
Starting Member

20 Posts

Posted - 2004-02-06 : 15:20:59

Ilimax,

A warning: this GROUP BY without an ORDER BY is going to lead to trouble for you. SQL Server tables are by definition unordered, and for grouping to behave like you'd expect requires ordering. GROUP BY does not implicitly order the set before grouping or aggregating. You could very easily end up with multiple records for the same item number.

Also, the normal solution for inventory control employs the concept of inventory transactions which could be stored into a table of their own. In this method, the current inventory can be calculated from the various transactions that impact it at any give point in time.

A trigger could be added to update a inventorystatus table if you wanted to tweak it out for performance. Your design may make it difficult to trace errors later, because it appears that you directly apply your quantity changes to a column at the time of the transaction.

I'm guessing what other parts of your tables & statements might look like here, so I could be wrong =)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources