Hi All
I am working on a query and have become stuck on how to proceed..
The query is returning data from 2 main tables ORDR (Customer Orders) and RDR1 (Lines on each order)
I'm trying to get the Total Discount amount per order but I'm not even sure how to go about it..
I believe I can get the total discount amount of each order line like this:
(Gross price each item - net price each item) * Quantity
(RDR1.PriceBefDi - RDR1.Price) * RDR1.Quantity
But how should I get the SUM of the discount amount of each line to find the Total amount of discount on the whole order?
I copy part of my query below for clarity and would very much appreciate any help or pointers..
SELECT
, [ORDR].[CardCode] AS [orderCustomerID]
, [ORDR].[PayToCode] AS [orderBillToCustomerID]
, [OTER].[TerritryID] AS [orderBillToTerritoryID]
, [OTER].[descript] AS [orderBillToTerritoryName]
, [ORDR].[DocNum] AS [orderNumber]
, CASE
WHEN [ORDR].[DocStatus] = 'C'
THEN 0
ELSE 1
END AS [orderOpen]
, CASE
WHEN [ORDR].[CANCELED] = 'Y'
THEN 1
ELSE 0
END AS [orderVoid]
, [ORDR].[NumAtCard] AS [orderCustomerPONumber]
, [ORDR].[DocDate] AS [orderDate]
, [ORDR].[GroupNum] AS [orderTerms]
, [RDR1].[LineStatus] AS [orderLineOpen]
, [RDR1].[LinManClsd] AS [orderLineVoid]
, [RDR1].[LineStatus] AS [orderLineStatus]
, [RDR1].[BaseRef] AS [orderQuoteNum]
, [RDR1].[BaseLine] AS [orderQuoteLine]
, [RDR1].[Quantity] AS [orderLineQty]
, [RDR1].[PriceBefDi] AS [orderPriceEAOrderedCurrency]
, ([RDR1].[PriceBefDi][RDR1].[DiscPrcnt]/100) AS [orderDiscountEAOrderedCurrency]
, [ORDR].[DocTotal][ORDR].[DocRate] AS [orderReleaseTotalPriceOrderedCurrency]
, ([ORDR].[DiscSum][ORDR].[DocRate]) AS [orderReleaseTotalDiscountOrderedCurrency]
, [ORDR].[VatSum] AS [orderReleaseTotalTaxOrderedCurrency]
, ([ORDR].[DocTotal][ORDR].[DocRate])-[ORDR].[VatSum] AS [orderReleaseAmountNetOrderedCurrency]
FROM ORDR
JOIN RDR1 ON ORDR.DocEntry = RDR1.DocEntry
JOIN OCRD ON ORDR.CardCode = OCRD.CardCode
JOIN CRD1 ON OCRD.CardCode = CRD1.CardCode AND CRD1.Address = ORDR.ShipToCode AND CRD1.AdresType = 'S'
JOIN OSLP ON ORDR.SlpCode = OSLP.SlpCode
JOIN OUSR ON ORDR.UserSign = OUSR.USERID
JOIN OTER ON OCRD.Territory = OTER.TerritryID
JOIN OITM ON RDR1.ItemCode = OITM.ItemCode
JOIN OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod