Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting Current Stock Report Expand / Collapse
Author
Message
Posted Thursday, November 7, 2013 9:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 1, 2013 10:54 PM
Points: 7, Visits: 15
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
Post #1512344
Posted Thursday, November 7, 2013 2:39 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:44 AM
Points: 5,018, Visits: 10,532
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1512458
Posted Sunday, November 10, 2013 2:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 1, 2013 10:54 PM
Points: 7, Visits: 15
Added more information to the post.
Post #1512952
Posted Sunday, November 10, 2013 7:33 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:37 AM
Points: 3,627, Visits: 5,270
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1512994
Posted Wednesday, November 13, 2013 10:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 1, 2013 10:54 PM
Points: 7, Visits: 15
Thanks a bunch Dwain,

Got an idea from your answer . I will modify the query as per my requirement.

Thanks again.

Arun
Post #1513978
Posted Wednesday, November 13, 2013 5:23 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:37 AM
Points: 3,627, Visits: 5,270
Glad to be of service.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1514113
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse