Skip to Content
0
Former Member
Jun 08, 2009 at 07:36 PM

Calculating total discount at the row level

902 Views

Hi,

I want to determine the total discount amount for all of the invoice row levels by summing the row totals that have the same discount % and then multiplying that total by the row discount percent.

Example:

Lets say there is an invoice with 3 line items

Qty ItemCode Price Discount%

2 A $30 10

2 B $20 10

2 C $15 5

I want to figure the total for the first 2 lines (because they have the same discount amount) by multiplying the quantity (230)+(220)=100 and then calculating the discount from this total. (100) * (.10) = $10 and then calculating the discount for the third line item ((215).05)=1.5. This brings the total discount amount to $11.50.

Below is what I have drafted for a query so far.

Select

//For each grouping of items with the same discount %

(SUM ((T0.Quantity * T0.PriceBefDi)) * DiscPrcnt)

FROM INV1 T0 WHERE T0.DocEntry=$[OINV.DocEntry]

Any ideas?

Thanks,

Luke