Manish Patat

Manish Patat

  • 2k
  • 66
  • 2.5k

store procedure Temp table create

Sep 15 2021 6:26 AM

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


Answers (3)