Skip to Content
avatar image
Former Member

query for item's opening and closing stock monthwise

Dear Experts,

I was needed a report of particular item's opening and closing stock month wise.

I will have to select from and to date and what date I select, item's opening and closing balance with value of that date given should retrieve.

Any suggestions will be highly appreciated.

Regards,

Ravi

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 09, 2013 at 10:40 AM

    Hi Ravi,

    Try this.

    /*SELECT FROM [dbo].[OINM] T10*/

    declare  @fromdate as datetime

    /* WHERE */

    set @fromdate = /* T10.DocDate */ '[%0]'

    /*SELECT FROM [dbo].[OINM] T11*/

    declare  @ToDate as datetime

    /* WHERE */

    set @ToDate = /* T11.DocDate */ '[%1]'

    select datename(MM,A.DocDate)+'/'+datename(YY,A.DocDate) as [Month],A.ItemCode,A.Dscription,

    (SELECT sum(T0.[InQty])-sum(T0.[OutQty])FROM OINM T0 where t0.ItemCode=A.ItemCode and T0.DocDate <@fromdate)OPStock,

    (select sum(U0.[TransValue]) FROM OINM U0 where U0.ItemCode=A.ItemCode and U0.DocDate <@fromdate)OpValue,

    (SELECT sum(T0.[InQty])-sum(T0.[OutQty])FROM OINM T0 where t0.ItemCode=A.ItemCode and T0.DocDate <=@ToDate)ClosingStock,

    (select sum(U0.[TransValue]) FROM OINM U0 where U0.ItemCode=A.ItemCode and U0.DocDate <=@ToDate)ClosingValue

    from OINM A where a.DocDate between @fromdate and @ToDate

    group by A.ItemCode,A.Dscription,datename(MM,A.DocDate)+'/'+datename(YY,A.DocDate)

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 09, 2013 at 09:07 AM

    Hi,

    Please check with this http://scn.sap.com/thread/1631705

    Thanks & Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Nagarajan,

      thanx for prompt reply.

      I checked with that query, in that query whenever INQty or Outqty is null in system, formula gets wrong. I've checked with some similar queries with formulation but the same issue, whenever is there null value in outqty or inqty results gets wrong both OB and Closing.

      Regards,

      Ravi

  • avatar image
    Former Member
    Oct 09, 2013 at 09:30 AM

    Hi Ravi,

    Try below Query

    /*SELECT FROM [dbo].[OINM] T10*/

    declare  @fromdate as datetime

    /* WHERE */

    set @fromdate = /* T10.DocDate */ '[%0]'

    /*SELECT FROM [dbo].[OINM] T11*/

    declare  @ToDate as datetime

    /* WHERE */

    set @ToDate = /* T11.DocDate */ '[%1]'

    select datename(MM,A.DocDate)+'/'+datename(YY,A.DocDate),A.ItemCode,A.Dscription,

    (SELECT sum(T0.[InQty])-sum(T0.[OutQty])FROM OINM T0 where t0.ItemCode=A.ItemCode and T0.DocDate <=dateadd(dd,-1,@fromdate))OPStock,

     

    (select sum(U0.[TransValue]) FROM OINM U0 where U0.ItemCode=A.ItemCode and U0.DocDate <=dateadd(dd,-1,@fromdate))OpValue,

    (SELECT sum(T0.[InQty])-sum(T0.[OutQty])FROM OINM T0 where t0.ItemCode=A.ItemCode and T0.DocDate <=dateadd(dd,-1,@ToDate))ClosingStock,

     

    (select sum(U0.[TransValue]) FROM OINM U0 where U0.ItemCode=A.ItemCode and U0.DocDate <=dateadd(dd,-1,@ToDate))ClosingValue

      

    from OINM A

    group by A.ItemCode,A.Dscription,datename(MM,A.DocDate)+'/'+datename(YY,A.DocDate)

    order by 1,2

    regards,

    Raviraj

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Ravi,

      Try below query

      /*SELECT FROM [dbo].[OINM] T10*/

      declare  @fromdate as datetime

      /* WHERE */

      set @fromdate = /* T10.DocDate */ '[%0]'

      /*SELECT FROM [dbo].[OINM] T11*/

      declare  @ToDate as datetime

      /* WHERE */

      set @ToDate = /* T11.DocDate */ '[%1]'

       

      select datename(MM,A.DocDate)+'/'+datename(YY,A.DocDate)as 'Month/Year',A.ItemCode,A.Dscription,

      (SELECT sum(T0.[InQty])-sum(T0.[OutQty])FROM OINM T0 where t0.ItemCode=A.ItemCode

      and  T0.DocDate <=dateadd(dd,-1,@fromdate))OPStock,

      (select sum(U0.[TransValue]) FROM OINM U0 where U0.ItemCode=A.ItemCode

      and U0.DocDate <=dateadd(dd,-1,@fromdate))OpValue,

      (SELECT sum(T0.[InQty])-sum(T0.[OutQty])FROM OINM T0 where t0.ItemCode=A.ItemCode

      and  T0.DocDate <=@ToDate)ClosingStock,

      (select sum(U0.[TransValue]) FROM OINM U0 where U0.ItemCode=A.ItemCode

      and  U0.DocDate <=@ToDate)ClosingValue

       

      from OINM A where A.DocDate between @fromdate and @ToDate

      group by A.ItemCode,A.Dscription,datename(MM,A.DocDate)+'/'+datename(YY,A.DocDate)

      order by 1,2

      regards,

      Raviraj

  • avatar image
    Former Member
    Oct 11, 2013 at 11:24 AM

    Hi Ganesh,

    Needed a little change in this query. Actually we have selected 5 items and we need only those items in this reports. Obviously this query reverts all items with transaction and this process take a long time b'coz of heavy item master data.

    So can you plz ammend this query for particular items, for example item codes are *1132000019*, *5130600101*, *1281000010*.

    Thanx in advance.

    Add comment
    10|10000 characters needed characters exceeded