Skip to Content
avatar image
Former Member

Want Modification in query?

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Nov 01, 2016 at 10:51 PM

    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

    Add comment
    10|10000 characters needed characters exceeded