Skip to Content
0

Want Modification in query?

Nov 01, 2016 at 08:03 PM

21

avatar image

Dear Expert

I have created following query base report

select t1.docnum,t1.cardname,t2.itemcode,t2.dscription,t2.price,t2.quantity as 'Po Qty',t3.quantity as 'Grn Qty',t2.OpenQty as 'Pending Qty', iif ((t2.quantity - t3.quantity)< 0,(t2.quantity - t3.quantity),0) as 'Excess Qty',(t2.openqty*t2.price) as 'Pending Amount',iif((t2.price* (t2.quantity - t3.quantity)) < 0 ,(t2.price* (t2.quantity - t3.quantity)),0)as 'Excess Amount'
from opor t1
inner join por1 t2 on t1.docentry = t2.docentry
left join PDN1 t3 on t2.trgetentry = t3.docentry and t3.basetype = t2.ObjType and t2.docentry = t3.BaseEntry and t2.itemcode = t3.itemcode
inner join opdn t4 on t3.docentry = t4.docentry

I want group by t1.docnum and t1.cardname, How we can do this?

Thanks and Kind Regards,

Mayur

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Nagarajan K Nov 01, 2016 at 10:51 PM
0

Hi,

Please try this query,

select

t1.docnum,t1.cardname,t2.itemcode,t2.dscription,t2.price,t2.quantity as 'Po Qty',t3.quantity as 'Grn Qty',t2.OpenQty as 'Pending Qty', iif ((t2.quantity - t3.quantity)< 0,(t2.quantity - t3.quantity),0) as 'Excess Qty',(t2.openqty*t2.price) as 'Pending Amount',iif((t2.price* (t2.quantity - t3.quantity)) < 0 ,(t2.price* (t2.quantity - t3.quantity)),0)as 'Excess Amount'

from opor t1 inner join por1 t2 on t1.docentry = t2.docentry left join PDN1 t3 on t2.docentry = t3.BaseEntry and t2.itemcode = t3.itemcode and T3.Baseline = T2.LineNum inner join opdn t4 on t3.docentry = t4.docentry

Group by t1.docnum,t1.cardname,t2.itemcode,t2.dscription,t2.price,t2.quantity,t2.OpenQty,t3.quantity

Thanks

Share
10 |10000 characters needed characters left characters exceeded