Stored Procedure do not work properly please help me.....
this Stored Procedure do not work properly it is run but do not calculate automatically...
For Example
ISNULL( (tp.qty-ts.qty),0) as Closing_Stock
this line is not work(purchase - sale quantity) if i change it for test to--
ISNULL( (tp.qty-1),0) as Closing_Stock
then it work and also same problem this line
ISNULL( (i.brate*(tp.qty-ts.qty)),0) As Amount
please help me
What I have tried:
select c.catnm, i.itemnm, i.opnstk, u.untnm, (i.ITMcgst*2) as StkGSTRate, ISNULL( tp.qty,0) as purchaseQty, ISNULL( TS.qty,0) AS SaleQty ,
ISNULL( tpr.qty,0) as Purchase_Return_QTY,ISNULL( tsr.qty,0) as Sale_Return_QTY , ISNULL( (tp.qty-ts.qty),0) as Closing_Stock
,ISNULL( i.brate,0) as brat,ISNULL( (i.brate*(tp.qty-ts.qty)),0) As Amount,lsd.lastSaleDate ,LPD.lastPurchaseDate
from tbl_item i
JOIN tblcat c ON c.catid=i.ITMcatid
JOIN tblUnit u ON u.untid=i.ITMunitid
LEFT JOIN (select ItemIDpur as itemid, sum(PDQty) As qty FROM TBL_pur_invdet where p_type='P' group by ItemIDpur) tp ON i.itemid=tp.itemid
LEFT JOIN (select ItemIDSALE as itemid, sum(SDQty) As qty FROM TBL_sale_invdet where S_type='S' group by ItemIDsale) ts ON i.itemid=tS.itemid
LEFT JOIN (select ItemIDpur as itemid, sum(PDQty) As qty FROM TBL_pur_invdet where p_type='PR' group by ItemIDpur) tpr ON i.itemid=tpr.itemid
LEFT JOIN (select ItemIDSALE as itemid, sum(SDQty) As qty FROM TBL_sale_invdet where S_type='SR' group by ItemIDsale) tsr ON i.itemid=tSr.itemid
LEFT JOIN (select ItemIDSALE as itemid, max(SaleInvdt) As lastSaleDate FROM TBL_sale_invdet where S_type='S' group by ItemIDsale) LSD ON i.itemid=LSD.itemid
LEFT JOIN (select ItemIDpur as itemid, max(purdate) As lastPurchaseDate FROM TBL_pur_invdet where P_type='P' group by ItemIDpur) LPD ON i.itemid=LPD.itemid