Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i write a stored procedure for Inventory Item stock report.
it work fine but when I add the where clause with date then it not work properly.
Here i used two table
SQL
TBL_sale_invdet
(sale Details)
SQL
TBL_pur_invdet
(Purchase Details)

I want to showing the result date wise means date wise stock details
in my data base last sale invoice date is -
SQL
02-03-2018(dd/mm/yyyy)

in my data base last Purchase invoice date is -
SQL
31-10-2017(dd/mm/yyyy)

When i run the SP date is
SQL
02-03-2018 to 02-03-2018 (dd/mm/yyyy)
nothing show in result.
but when
SQL
31-10-2017 to 02-03-2018 (dd/mm/yyyy)

show some result but not correct, show only which item match with sale and purchase show '5' item i have '174' item
how can i show all item date wise with item sale or purchase or return both
please help me...........

What I have tried:

SQL
ALTER Proc [dbo].[P_getAllItemStock]
@Sdate date,            
@EDate Date 
as
begin 
select  c.catnm, i.itemnm, i.opnstk, u.untnm,i.itmcod,(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, 0) - ISNULL(ts.qty, 0) as Closing_Stock
,ISNULL( i.brate,0) as brat,i.brate * (ISNULL(tp.qty, 0) - ISNULL(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
    join TBL_pur_invdet pd on pd.ItemIDpur=i.itemid
    join TBL_sale_invdet sd on sd.ItemIDsale=i.itemid
    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
 
   where  CAST( purdate as date ) BETWEEN @Sdate and @EDate and CAST( sd.SaleInvdt as date ) BETWEEN @Sdate and @EDate --and (ISNULL(tp.qty,0)-ISNULL(ts.qty,0))>0
 group by catnm, itemnm, opnstk,untnm,itmcod,ITMcgst,tp.qty,ts.qty,tpr.qty,tsr.qty,brate,lastSaleDate,lastPurchaseDate
  order BY C.catnm
end
Posted
Updated 1-Mar-18 21:35pm

1 solution

Instead of use CAST, use CONVERT that permits to tell what kind of format should be use to scan the string

Instead
SQL
CAST(xx as date)

use
SQL
CONVERT(date,xx, 105)


In the documentation you can see that 105 means that the string is in the format dd-mm-aa
 
Share this answer
 
Comments
Jayanta Modak 2-Mar-18 3:56am    
Msg 1035, Level 15, State 10, Line 20
Incorrect syntax near 'CAST', expected 'AS'.
Member 7870345 2-Mar-18 4:16am    
Please replace CAST( purdate as date ) with CONVERT(date, purdate,105)
and replace CAST( sd.SaleInvdt as date ) with CONVERT(date, sd.SaleInvdt,105).
In general dont use the function CAST to convert to date instead use the CONVERT function with tree parameters to control how the string must be interpreted.
Jayanta Modak 2-Mar-18 5:54am    
no error but result is same

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900