1 Replies - 644 Views - Last Post: 01 March 2018 - 12:10 PM Rate Topic: -----

#1 [email protected]   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 90
  • Joined: 16-May 17

Stored Procedure do not work properly please help me.....

Posted 01 March 2018 - 11:42 AM

this Stored Procedure do not work properly it is run but do not calculate automatically...


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
    


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 this line
ISNULL( (i.brate*(tp.qty-ts.qty)),0) As Amount
please help me

Is This A Good Question/Topic? 0
  • +

Replies To: Stored Procedure do not work properly please help me.....

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14929
  • View blog
  • Posts: 59,615
  • Joined: 12-June 08

Re: Stored Procedure do not work properly please help me.....

Posted 01 March 2018 - 12:10 PM

ISNULL( (tp.qty-ts.qty),0) as Closing_Stock

Why would you have an 'is null' function call around a math formula? First I would make sure both are not null.. barring that put an 'isnull' around each.


Example: ISNULL(tp.qty, 0)- isnull(ts.qty,0)
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1