Hi
I have three tables in my db called Products, Receives, Issues.
Products
ProductID, ProductName
Receives
ProductID, ReceiveQty
Issues
ProductID, IssueQty
I want to pass an ProductID and display columns from a sproc like this -
ProductName, ReceiveQty, IssueQty, BalanceQty ( ReceiveQty - IssueQty )
A record may exist or not in the Issues table for a given ProductID. What will be the syntex to write this sproc?
Regards
Kapalic
Something like this:
Code Snippet
SELECT
p.ProductName,
isnull( r.ReceiveQty, 0 ),
isnull( i.IssueQty, 0 ),
BalanceQty = ( isnull( r.ReceiveQty, 0 ) - isnull( i.IssueQty, 0 ))
FROM Products p
JOIN Receives r
ON p.ProductID = r.ProductID
LEFT JOIN Issues i
On p.ProductID = i.ProductID
I must note: this seems like a very odd design, and may be troublesome to properly 'tune'.
|||Try the following, it may be helpful for you.
Code Snippet
select ProductName, ReceiveQty, IssueQty, (ReceiveQty-IssueQty) as BalanceQtyfrom
(
select Products.ProductName,
isnull((select sum(ReceiveQty) from Receives where Receives.ProductID=Products.ProductID),0) as ReceiveQty,
isnull((select sum(IssueQty) from Issues where Issues.ProductID=Products.ProductID),0) as IssueQty
from Products where Products.ProductID = yourProductId
) as temp
|||to:Arnie Rowland
what will happen if i recieve a product 2 times or more?
|||You make a good observation. I should have included a [GROUP BY].
I think that this revision will be more in line with an acceptable solution:
Code Snippet
SELECT
p.ProductName,
p.ProductID,
dt.ReceiveQty,
dt.IssueQty,
dt.BalanceQty
FROM Products p
JOIN (SELECT
p.ProductID,
ReceiveQty = sum( isnull( r.ReceiveQty, 0 )),
IssueQty = sum( isnull( i.IssueQty, 0 )),
BalanceQty = sum( isnull( r.ReceiveQty, 0 ) - isnull( i.IssueQty, 0 ))
FROM Products p
LEFT JOIN Receives r
ON p.ProductID = r.ProductID
LEFT JOIN Issues i
On p.ProductID = i.ProductID
WHERE p.ProductID = @.ProductID
GROUP BY ProductID
) dt
ON p.ProductID = dt.ProductID
WHERE p.ProductID = @.ProductID
IssueQty will be 0 (zero) if no rows exist in the Issues table.
ReceiveQty will be 0 (zero) if no rows exist in the Receives table.
|||Arnie
I m getting incorrect result - while I enter multiple receive record, every of three qty become double of actual result! Whats the prob?
Kapalic
|||In order by continue assisting you, it would be useful to have sample data to test.
Please include table DDL and sample data in the form of INSERT statements.
|||Kapalic,
First, my apologies. I was quickly tossing code together without thoroughly thinking through your situation. After your last post, I realized that I had made a significant error in what I previous posted.
Hopefully, this code will get you what you want.
-Arnie
Code Snippet
DECLARE @.Products table
( ProductID int,
ProductName varchar(25)
)
DECLARE @.Receives table
( ProductID int,
ReceiveQty int
)
DECLARE @.Issues table
( ProductID int,
IssueQty int
)
INSERT INTO @.Products VALUES ( 1, 'Product1' )
INSERT INTO @.Products VALUES ( 2, 'Product2' )
INSERT INTO @.Products VALUES ( 3, 'Product3' )
INSERT INTO @.Products VALUES ( 4, 'Product4' )
INSERT INTO @.Products VALUES ( 5, 'Product5' )
INSERT INTO @.Receives VALUES ( 1, 5 )
INSERT INTO @.Receives VALUES ( 2, 12 )
INSERT INTO @.Receives VALUES ( 3, 10 )
INSERT INTO @.Receives VALUES ( 4, 25 )
INSERT INTO @.Receives VALUES ( 1, 10 )
INSERT INTO @.Receives VALUES ( 2, 10 )
INSERT INTO @.Receives VALUES ( 1, 12 )
INSERT INTO @.Issues VALUES ( 1, 2 )
INSERT INTO @.Issues VALUES ( 2, 5 )
INSERT INTO @.Issues VALUES ( 1, 5 )
INSERT INTO @.Issues VALUES ( 3, 10 )
INSERT INTO @.Issues VALUES ( 2, 1 )
INSERT INTO @.Issues VALUES ( 1, 5 )
INSERT INTO @.Issues VALUES ( 5, 5 )
DECLARE @.ProductID int
SET @.ProductID = 1
-- This code could become a FUNCTION to return only BalanceQty,
-- or a STORED PROCEDURE to return all current values
SELECT
p.ProductName,
p.ProductID,
ReceiveQty = ( isnull( dt.ReceiveQty, 0 )),
IssueQty = ( isnull( dt.IssueQty, 0 )),
BalanceQty = ( isnull( dt.ReceiveQty, 0 ) - isnull( dt.IssueQty, 0 ))
FROM (SELECT
ProductID = @.ProductID,
ReceiveQty = ( SELECT sum( isnull( ReceiveQty, 0 ))
FROM @.Receives
WHERE ProductID = @.ProductID
),
IssueQty = ( SELECT sum( isnull( IssueQty, 0 ))
FROM @.Issues
WHERE ProductID = @.ProductID
)
) dt
JOIN @.Products p
ON p.ProductID = dt.ProductID
WHERE p.ProductID = @.ProductID
Dear Arnie,
Thank you very much for your great help! This sproc is now working allright. Will you please tell me what was wrong with the sproc?
And I need a bit more help. I can't understand how to modify the sproc so that it displays all the products which are listed in the product table. Please help!!
Regards
Kapalic
|||
Code Snippet
SELECTp.ProductName,
p.ProductID,
p.AdditionalProductsColumnsHere,
ReceiveQty = ( isnull( dt.ReceiveQty, 0 )), You can add additional columns from the Products table in the outer query. Prefix each column with the alias 'p'.
|||
I didn't want to show another column. I wanted to show all the records from the products table, not just a single record about a product. I couldn't make myself clear.
Thnx
Kapalic
|||
Arnie Rowland wrote:
Kapalic,
First, my apologies. I was quickly tossing code together without thoroughly thinking through your situation. After your last post, I realized that I had made a significant error in what I previous posted.
Hopefully, this code will get you what you want.
-Arnie
Code Snippet
DECLARE @.Products table( ProductID int,
ProductName varchar(25)
)
DECLARE @.Receives table( ProductID int,
ReceiveQty int
)
DECLARE @.Issues table( ProductID int,
IssueQty int
)
INSERT INTO @.Products VALUES ( 1, 'Product1' )
INSERT INTO @.Products VALUES ( 2, 'Product2' )
INSERT INTO @.Products VALUES ( 3, 'Product3' )
INSERT INTO @.Products VALUES ( 4, 'Product4' )
INSERT INTO @.Products VALUES ( 5, 'Product5' )INSERT INTO @.Receives VALUES ( 1, 5 )
INSERT INTO @.Receives VALUES ( 2, 12 )
INSERT INTO @.Receives VALUES ( 3, 10 )
INSERT INTO @.Receives VALUES ( 4, 25 )
INSERT INTO @.Receives VALUES ( 1, 10 )
INSERT INTO @.Receives VALUES ( 2, 10 )
INSERT INTO @.Receives VALUES ( 1, 12 )INSERT INTO @.Issues VALUES ( 1, 2 )
INSERT INTO @.Issues VALUES ( 2, 5 )
INSERT INTO @.Issues VALUES ( 1, 5 )
INSERT INTO @.Issues VALUES ( 3, 10 )
INSERT INTO @.Issues VALUES ( 2, 1 )
INSERT INTO @.Issues VALUES ( 1, 5 )
INSERT INTO @.Issues VALUES ( 5, 5 )
DECLARE @.ProductID int
SET @.ProductID = 1
-- This code could become a FUNCTION to return only BalanceQty,
-- or a STORED PROCEDURE to return all current values
SELECT
p.ProductName,
p.ProductID,
ReceiveQty = ( isnull( dt.ReceiveQty, 0 )),
IssueQty = ( isnull( dt.IssueQty, 0 )),
BalanceQty = ( isnull( dt.ReceiveQty, 0 ) - isnull( dt.IssueQty, 0 ))
FROM (SELECT
ProductID = @.ProductID,ReceiveQty = ( SELECT sum( isnull( ReceiveQty, 0 ))
FROM @.Receives
WHERE ProductID = @.ProductID
),
IssueQty = ( SELECT sum( isnull( IssueQty, 0 ))
FROM @.Issues
WHERE ProductID = @.ProductID
)
) dt
JOIN @.Products p
ON p.ProductID = dt.ProductID
WHERE p.ProductID = @.ProductID
Can you tell me how to post a sample code with color?
|||It's very easy. Copy the code to MS Word, and copy and paste it from Word to post. It will appear colorful.
Kapalic
