I have invoices:
Invoice No: 10001
Discount: $30
ItemA, 2 Pcs, total $100 => net $100-$10 = $90
ItemB, 2 Pcs, total $200 => net $200-$10 = $190
ItemC, 2 Pcs, total $300 => net $300-$10 = $290
because $30 discount is distributed evenly to each item, regarding the item value and qty
Invoice No: 10002
Discount: $40
ItemA, 2 Pcs, total $100 => net $100-$20 = $80
ItemB, 2 Pcs, total $200 => net $200-$20 = $180
each item gets $20 discount, regarding the item value and qty
I expect the report as:
Group by item:
ItemA, 4 Pcs, total $170 (90+80)
ItemB, 4 Pcs, total $370 (190+180)
ItemC, 2 Pcs, total $290 (290)
so instead of this query:
select cItem,sum(nQty),sum(nAmount) from invoice group by cItem
I want to do:
select cItem,sum(nQty),sum(nAmount-nDisc/nrecord) from invoice group by cItem
where nrecord is:
select count(*) as nrecord from invoice group by cinvoice
Is that possible to do it in one query?
Thanks for the help.
Regards,
Jerry Yang