I Have Stock maintain table create through store procedure
My Data Store Different Table look like This
I Have Perform Store Procedure
ALTER PROCEDURE [dbo].[Sp_purchasestock]
@SDT as date=GetDate,
@EDT as date=GetDate
AS
BEGIN
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempStock]') AND type in (N'U'))
Drop Table TempStock
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempStock]') AND type in (N'U'))
BEGIN
Create TABLE [dbo].[TempStock]
[ITEMCODE] nvarchar(20) NULL
[GRNQty] decimal(15,3) NULL,
[IssuedQty] decimal(15,3) NULL,
[StockQty] decimal(15,3) NULL,
[StockValue] decimal (15,3) Null)
End
BEGIN
INSERT INTO TempStock
SELECT
dbo.POItemMaster.ITEMCODE,
(Select isnull(Sum(AcceptQty),0)+isnull(Sum(AcceptQtyUD),0) from POGRN
Where ItemCode=POItemMaster.ITEMCODE ) As GRNQty,
(SELECT isnull(SUM(IssuedQty),0) AS Expr1 FROM dbo.IssueMaterial AS IssueMaterial_1 WHERE (ItemCode = dbo.POItemMaster.ITEMCODE)) AS IssuedQty,
((SELECT ISNULL(SUM(AcceptQty),0)+ISNULL(SUM(AcceptQtyUD),0) FROM dbo.POGRN
WHERE (POGRN.ItemCode = dbo.POItemMaster.ITEMCODE) )-
(SELECT ISNULL(SUM(IssuedQty),0) FROM IssueMaterial WHERE (IssueMaterial.ItemCode = dbo.POItemMaster.ITEMCODE)
))+(SELECT ISNULL(SUM(AcceptQty),0)+ISNULL(SUM(AcceptQtyUD),0) AS Expr1 FROM dbo.POGRN AS POGRN_1 WHERE (ItemCode = dbo.POItemMaster.ITEMCODE) ) - (SELECT ISNULL(SUM(IssuedQty),0) AS Expr1
FROM dbo.IssueMaterial AS IssueMaterial_1 WHERE (ItemCode = dbo.POItemMaster.ITEMCODE) )
AS STOCKQty,
((SELECT ISNULL(SUM(POGRN.AcceptQty+POGRN.AcceptQtyUD) * PODetailSub.RATE), 0) FROM dbo.POGRN INNER JOIN PODetailSub ON PODetailSub.PONO = POGRN.PONO AND PODetailSub.POLI = POGRN.POLI
WHERE (POGRN.ItemCode = dbo.POItemMaster.ITEMCODE))
-(SELECT ISNULL(SUM(IssuedQty * RATE), 0) FROM IssueMaterial INNER JOIN POGRN ON IssueMaterial.GRNNo = POGRN.GRNNO And IssueMaterial.GRNID = POGRN.GRNID AND IssueMaterial.ItemCode = POGRN.ItemCode
INNER JOIN PODetailSub ON PODetailSub.PONO = POGRN.PONO AND PODetailSub.POLI = POGRN.POLI WHERE (IssueMaterial.ItemCode = dbo.POItemMaster.ITEMCODE) ))
+ (SELECT ISNULL(SUM(POGRN.AcceptQty+POGRN.AcceptQtyUD) * PODetailSub.RATE), 0) AS Expr1 FROM dbo.POGRN INNER JOIN PODetailSub ON PODetailSub.PONO = POGRN.PONO AND PODetailSub.POLI = POGRN.POLI WHERE (POGRN.ItemCode = dbo.POItemMaster.ITEMCODE) )
- (SELECT ISNULL(SUM(IssuedQty * RATE), 0) AS Expr1 FROM dbo.IssueMaterial AS IssueMaterial INNER JOIN POGRN ON IssueMaterial.GRNNo = POGRN.GRNNO And IssueMaterial.GRNID = POGRN.GRNID INNER JOIN PODetailSub ON PODetailSub.PONO = POGRN.PONO AND PODetailSub.POLI = POGRN.POLI WHERE (IssueMaterial.ItemCode =dbo.POItemMaster.ITEMCODE) ) AS StockValue
FROM dbo.POItemMaster
End
select * from TempStock
END
Output Look Like This Complete
But My Stock Value Calculation Not
Thank in Advance