SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting Current Stock Report


Getting Current Stock Report

Author
Message
darunsurey
darunsurey
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 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
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24399 Visits: 13362
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
darunsurey
darunsurey
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 15
Added more information to the post.
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18119 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
darunsurey
darunsurey
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 15
Thanks a bunch Dwain,

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

Thanks again.

Arun
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18119 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search