Skip to Content
0
Former Member
Aug 06, 2008 at 03:07 PM

SAB B1 Query

265 Views

I am new to B1 Query and I am trying to create a report to show a summary of order discounts by customer for a date range. There are discounts at the line level and at the order level. When I run the following query, it adds the order level discount and order level total to the summary for each order line. I can't figure out how to summarize the info from INV1 and pull in the order total info just once per order. Any ideas would be appreciated. Here is my code:

SELECT T1.CardName, sum(T0.QuantityT0.PriceBefDi)[Total Before Discounts], sum((T0.QuantityT0.PriceBefDi)-T0.LineTotal)[Line Discount], Sum(T1.DiscSum)[Order Discount],

Sum(((T0.Quantity*T0.PriceBefDi)-T0.LineTotal)+T1.DiscSum)[Total Discounts],

Sum(T1.TotalExpns)[Freight],Sum(T1.DocTotal)[Invoice Total]

FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry

WHERE T1.DocDate between [%1] and [%2]

Group by T1.CardName

Order By T1.CardName