NEED SOME MODIFICATION IN QUERY FOR FINDING FAST MOVING AND SLOW MOVING ITEMS

Jan 31, 2017 at 05:59 AM|48 Views

Hi experts

i had made a query for getting the fast and slow moving items Based on OINM,OITM,OITW

also need to get the current Stock in Hand.

where i need a item in a single line, but where now its repeating more than one.

pls help me by editing the query

select Distinct OITM.ItemCode,OITM.ItemName,sum(OutQty)'Net Out From System',sum(OITW.OnHand) as 'Stock in Hand' from OINM
Inner join OITM on OINM.ItemCode=OITM.ItemCode
inner JOIN OITW on OINM.ItemCode = OITW.ItemCode and OINM.Warehouse = OITW.WhsCode
where Warehouse = 'JB-WH' AND DocDate BETWEEN '2016-06-01 00:00:00.000' AND '2017-01-30 00:00:00.000'
group by OITM.ItemCode,OITM.ItemName,OITW.OnHand,OutQty
order by [Net Out From System] asc

select Distinct OITM.ItemCode
,OITM.ItemName
,sum(OutQty) as 'Net Out From System' /* the sum function already groups these fields */
,sum(OITW.OnHand) as 'Stock in Hand' /* so do not include them in the group by clause */
from OINM
Innerjoin OITM on OINM.ItemCode = OITM.ItemCode
innerJOIN OITW on OINM.ItemCode = OITW.ItemCode and OINM.Warehouse = OITW.WhsCode
where Warehouse = 'JB-WH'
AND DocDate BETWEEN'2016-06-01 00:00:00.000'AND'2017-01-30 00:00:00.000'
group by OITM.ItemCode
,OITM.ItemName
orderby[Net OutFromSystem] asc