November 7, 2013 at 9:40 am
Hi All,
I have a table for maintaining stocks as follows,
InventoryId - bigint
TransactionDate - datetime
InwardId - int
ProductId - int
InventoryStatus - int (0 - Available, 1 - Sold)
ReferenceNo - nvarchar(50)
Description - nvarchar(1000)
TotalItems - int
I am using the below query to get stock summary,
DECLARE @SoldProducts TABLE (ProductId INT,Product VARCHAR(50), TotalItems INT)
DECLARE @AvailableProducts TABLE (ProductId INT,Product VARCHAR(50), TotalItems INT)
INSERT @SoldProducts (ProductId,Product,TotalItems)
SELECT ProductId,ProductTitle,Sum(isnull(TotalItems,0)) FROM Inventory inv
JOIN
ProductMaster product
ON
inv.ProductId = product.ProductMasterId
WHERE
InventoryStatus = 1
GROUP BY ProductTitle,ProductId
INSERT @AvailableProducts (ProductId,Product,TotalItems)
SELECT ProductId,ProductTitle,SUM(isnull(TotalItems,0)) FROM Inventory inv
JOIN
ProductMaster product
ON
inv.ProductId = product.ProductMasterId
WHERE
InventoryStatus = 0
GROUP BY ProductTitle,ProductId
SELECT available.Product, SUM(ISNULL(available.TotalItems,0)) - SUM(ISNULL(sold.TotalItems,0)) AS TotalItems FROM
@AvailableProducts available
LEFT JOIN @soldproducts sold
ON
sold.ProductId = available.ProductId
GROUP BY available.ProductId,available.Product
I am getting the results for the above query correctly only when products contains both sold and available products. If either status of the inventory is not found then it does not show the product in the result.
For Example consider the below data,
1. Product id 1 contains both status products
INSERT INTO inventory(InwardId,InventoryStatus,ReferenceNo,Description,TotalItems,TransactionDate,ProductId)
VALUES (29,0,'','',10,GETDATE(),1)
INSERT INTO inventory(InwardId,InventoryStatus,ReferenceNo,Description,TotalItems,TransactionDate,ProductId)
VALUES (29,1,'','',5,GETDATE(),1)
1. Product id 2 contains both status products
INSERT INTO inventory (InwardId,InventoryStatus,ReferenceNo,Description,TotalItems,TransactionDate,ProductId)
VALUES (30,0,'','',10,GETDATE(),2)
INSERT INTO inventory(InwardId,InventoryStatus,ReferenceNo,Description,TotalItems,TransactionDate,ProductId)
VALUES (31,1,'','',5,GETDATE(),2)
3. Product id 3 contains only available status products
INSERT INTO inventory (InwardId,InventoryStatus,ReferenceNo,Description,TotalItems,TransactionDate,ProductId)
VALUES (31,0,'','',10,GETDATE(),3)
4. Product id 4 contains only sold status products
INSERT INTO inventory (InwardId,InventoryStatus,ReferenceNo,Description,TotalItems,TransactionDate,ProductId)
VALUES (31,1,'','',5,GETDATE(),4)
when i am querying with the above data I am getting the result but i am getting only for the product id 1 and 2. I am expecting to display product 3 and 4 total items as 10 and (-5) respectively.
Can anyone pls help me?
Thanks in advance.
Arun
November 7, 2013 at 2:39 pm
Lots of information missing. Please read the first article linked in my signature line and find out how to post effectively to get quick answers to your questions.
-- Gianluca Sartori
November 10, 2013 at 2:37 am
Added more information to the post.
November 10, 2013 at 7:33 pm
Possibly what you are looking for is something like this:
DECLARE @Inventory TABLE
(
InventoryId bigint IDENTITY
,TransactionDate datetime
,InwardId int
,ProductId int
,InventoryStatus int -- (0 - Available, 1 - Sold)
,ReferenceNo nvarchar(50)
,[Description] nvarchar(1000)
,TotalItems int
);
INSERT INTO @inventory(InwardId,InventoryStatus,ReferenceNo,[Description],TotalItems,TransactionDate,ProductId)
VALUES (29,0,'','',10,GETDATE(),1);
INSERT INTO @inventory(InwardId,InventoryStatus,ReferenceNo,[Description],TotalItems,TransactionDate,ProductId)
VALUES (29,1,'','',5,GETDATE(),1);
INSERT INTO @inventory (InwardId,InventoryStatus,ReferenceNo,[Description],TotalItems,TransactionDate,ProductId)
VALUES (30,0,'','',10,GETDATE(),2);
INSERT INTO @inventory(InwardId,InventoryStatus,ReferenceNo,[Description],TotalItems,TransactionDate,ProductId)
VALUES (31,1,'','',5,GETDATE(),2);
INSERT INTO @inventory (InwardId,InventoryStatus,ReferenceNo,[Description],TotalItems,TransactionDate,ProductId)
VALUES (31,0,'','',10,GETDATE(),3);
INSERT INTO @inventory (InwardId,InventoryStatus,ReferenceNo,[Description],TotalItems,TransactionDate,ProductId)
VALUES (31,1,'','',5,GETDATE(),4);
DECLARE @ProductMaster TABLE (ProductMasterId INT,ProductTitle VARCHAR(50));
INSERT INTO @ProductMaster
VALUES (1, 'A'),(2, 'B'),(3, 'C'),(4, 'D');
SELECT Product=ProductTitle
,TotalItems=SUM(CASE WHEN b.TotalItems IS NULL THEN 0
WHEN b.InventoryStatus = 1 THEN -b.TotalItems ELSE b.TotalItems END)
FROM @ProductMaster a
LEFT JOIN @inventory b ON a.ProductMasterID = b.ProductID
GROUP BY ProductTitle;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 13, 2013 at 10:19 am
Thanks a bunch Dwain,
Got an idea from your answer 🙂 . I will modify the query as per my requirement.
Thanks again.
Arun
November 13, 2013 at 5:23 pm
Glad to be of service.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply