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
TBL_sale_invdet
(sale Details)
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 -
02-03-2018(dd/mm/yyyy)
in my data base last Purchase invoice date is -
31-10-2017(dd/mm/yyyy)
When i run the SP date is
02-03-2018 to 02-03-2018 (dd/mm/yyyy)
nothing show in result.
but when
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:
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
group by catnm, itemnm, opnstk,untnm,itmcod,ITMcgst,tp.qty,ts.qty,tpr.qty,tsr.qty,brate,lastSaleDate,lastPurchaseDate
order BY C.catnm
end