Skip to Content
0
Former Member
Aug 04, 2011 at 06:24 AM

how to extract transaction price in OINM

35 Views

Hi experts!

We have a crystal report. One portion of the report is supposed to show the total inventory issued out. It shows the correct quantity, but picks up the current price of the item instead of the price used during the transaction. Can anyone please help me edit the script? I'm relatively new to scripting..

select CAST(MONTH(a.Docdate) AS varchar(2)) + '/' + CAST(YEAR(a.Docdate) AS varchar(4))as DATEGROUP,

a.Docdate,

a.transtype,

a.itemcode,

b.itmsgrpcod,

c.U_CMR_Code,

sum(a.inqty) as 'INQTY',

sum(a.outqty) as 'OUTQTY',

sum(a.outqty)-sum(a.inqty)as 'Qty Difference',

a.price,

c.cyclecode,

d.code

FROM OINM a

LEFT OUTER JOIN OITM b on a.itemcode=b.itemcode

LEFT OUTER JOIN OITB c on b.itmsgrpcod=c.itmsgrpcod

LEFT OUTER JOIN [dbo].[@CMR_CODE] d on c.u_cmr_code=d.code

where a.transtype in ('59','60')

GROUP BY a.Docdate, a.transtype, a.itemcode, a.price, b.itmsgrpcod,c.u_cmr_code,c.cyclecode,d.code

thanks!

Tessa