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