Skip to Content
avatar image
Former Member

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

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
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Jan 31, 2017 at 07:11 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 31, 2017 at 06:59 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 31, 2017 at 08:14 AM

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

    Add comment
    10|10000 characters needed characters exceeded