stock statement query--- help

[Former Member]

Have any one got the stock statement query report in the folowing format....

1.ItemCode

2.Item Description

3.Item Price

4.Opening

5.Receipts

6.Issues

7.Closing

8. Value

The selection criteria are:

3.Posting Date

from wh

to wh

help me in making stock statement query

regards,

Vignesh

[Former Member · Accepted solution]

Hi Vignesh......

Check this path......

Inventory> Inventory Reports> Inventory Posting List.....

This report will meet your exact requirement and as its standard report so it will give your proper data also.....

Regards,

Rahul

[Former Member]

You may check this first:

Thanks,

Gordon

[former_member206488]

Hi,

Check thread

Thanks,

Neetu

[Former Member]

hi rahul,

thanks for u reply. yes there is an option in inventory, but my customer wants to know how much stock was there on a specific date from all ware house... the selection criteria should be from and to date....

regards,

Vignesh

[Former Member]

Hi Vignesh.........

If your cleint want it that way then its a perfect report for you.

You need to just give From Dt and To Dt and also select the appropriate Whse.....

Regards,

Rahul

[Former Member]

hi rahul,

i dont have any query like that to make it as a perfect report... need query.

regards,

Vignesh

[Former Member]

Hi

Check This

declare @date smalldatetime
declare @ic varchar(15) 
select @ic = $[$38.1.0], @date = $[$12.1.date]
select w0.itemcode, @date, w0.whscode, w0.onhand - isnull (sum (i0.inqty - i0.outqty), 0), 0
from oitw w0 left outer join oinm i0 on w0.itemcode = i0.itemcode and w0.whscode = i0.warehouse
where i0.docduedate >= @date
group by w0.itemcode, w0.whscode, w0.onhand
having w0.itemcode = @ic and w0.onhand - isnull (sum (i0.inqty - i0.outqty), 0) >= 0
union
select w0.itemcode, @date, w0.whscode, 0, -(w0.onhand - isnull (sum (i0.inqty - i0.outqty), 0))
from oitw w0 left outer join oinm i0 on w0.itemcode = i0.itemcode and w0.whscode = i0.warehouse
where i0.docduedate >= @date
group by w0.itemcode, w0.whscode, w0.onhand
having w0.itemcode = @ic and w0.onhand - isnull (sum (i0.inqty - i0.outqty), 0) < 0

Thanks

Kevin

[Former Member]

select @ic = $[$38.1.0], @date = $[$12.1.date]... it is showing an error in this one.... wat does it resemble.... i am not getting the output

regards,

Vignesh

[Former Member]

Hi

Check This

select w0.itemcode, i0.docdate, w0.whscode, w0.onhand - isnull (sum (i0.inqty - i0.outqty), 0), 0
from oitw w0 left outer join oinm i0 on w0.itemcode = i0.itemcode and w0.whscode = i0.warehouse
where i0.docduedate >= [%0]
group by w0.itemcode, w0.whscode, w0.onhand, i0.docdate
having w0.itemcode = [%1] and w0.onhand - isnull (sum (i0.inqty - i0.outqty), 0) >= 0
union
select w0.itemcode, i0.docdate, w0.whscode, 0, -(w0.onhand - isnull (sum (i0.inqty - i0.outqty), 0))
from oitw w0 left outer join oinm i0 on w0.itemcode = i0.itemcode and w0.whscode = i0.warehouse
where i0.docduedate >= [%0]
group by w0.itemcode, w0.whscode, w0.onhand, i0.docdate
having w0.itemcode = [%1] and w0.onhand - isnull (sum (i0.inqty - i0.outqty), 0) < 0

or

SELECT T0.ITEMCODE, T0.DSCRIPTION,T0.OPENQTY, T0.OPENVALUE, T0.INQTY, T0.INQTY*T0.PRICE AS 'RCPT VAL', T0.OUTQTY,T0.OUTQTY*T0.PRICE AS 'ISS. VAL', T0.INQTY-T0.OUTQTY AS 'CLOSING STOCK QTY',((T0.INQTY-T0.OUTQTY)*(T0.CALCPRICE)) AS 'CLOSING STOCK VAL' FROM OINM T0 WHERE T0.DOCDATE >=[%0] AND T0.DOCDATE <=[%1] 

Thanks

Kevin

Edited by: Kevin Shah on Oct 23, 2010 5:37 PM