Skip to Content
0

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

Jan 31, 2017 at 05:59 AM

38

avatar image

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
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Nagarajan K Jan 31, 2017 at 07:11 AM
1

Hi,

Try this,

select OITM.ItemCode ,OITM.ItemName ,

sum(OutQty) as 'Net Out From System' /* the sum function already groups these fields */ ,

OITW.OnHand as 'Stock in Hand' /* so do not include them in the group by clause */

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'2006-06-01 00:00:00.000'AND'2017-01-30 00:00:00.000'

group by OITM.ItemCode ,OITM.ItemName,OITW.OnHand

order by OITM.ItemCode, [Net Out From System] asc

Show 1 Share
10 |10000 characters needed characters left characters exceeded

GOOD ONE ITS WORKING

0
Johan Hakkesteegt Jan 31, 2017 at 06:59 AM
0

Hi,

Please try this:

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

Regards,

Johan

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Jan 31, 2017 at 08:14 AM
0

Did you run my query? Also compare both queries and check result.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

SIR

I WILL CHECK AND RETRIVE BACK

0