query for item's opening and closing stock monthwise

[former_member209725]

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

[Former Member · Accepted solution]

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)

[former_member209725 · Accepted solution]

Hi Ganesh,

Thanx for reply.

query running good. Thanx for consideration.

Regards,

Ravi

[nyasa]

Hi Ravi,

Opening stock of one month is not matching with closing stock of previous month, then how the report is correct

[former_member209725]

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.

[Former Member]

Hi,

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/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 and A.ItemCode not in ('1132000019', '5130600101', '1281000010')

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

order by 1,2

rehards,

Raviraj

[Former Member]

Hi,

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/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 and A.ItemCode not in ('1132000019', '5130600101', '1281000010')

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

order by 1,2

rehards,

Raviraj

[former_member209725]

hi Raviraj,

Thanx boss, it's completely working.

Regards,

Ravi

[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),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

[former_member209725]

Hi Raviraj,

Thanx for reply.

in the first column date is soming from April/2010 to sep/2013, but I am selecting date from 01-04-2013 to 30-04-13.

Plz check

Regards,

Ravi

[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

[kothandaraman_nagarajan]

Hi,

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

Thanks & Regards,

Nagarajan

[former_member209725]

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