No idea if this is correct, but it may get you started.
Create some tables with test data.
DROP TABLE IF EXISTS dbo.ORDR; --Customer Orders
DROP TABLE IF EXISTS dbo.RDR1; --Lines on each order
GO
CREATE TABLE dbo.ORDR(
DocEntry int NOT NULL PRIMARY KEY,
DocNum int NOT NULL,
OrderDate Date NOT NULL
)
CREATE TABLE dbo.RDR1(
id int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
DocEntry int NOT NULL,
PriceBefDi float NOT NULL,
Price float NOT NULL,
Quantity int NOT NULL
)
GO
INSERT INTO dbo.ORDR (DocEntry, DocNum, OrderDate) VALUES
(1, 100, '20200201'),
(2, 101, '20200202')
INSERT INTO dbo.RDR1(DocEntry, PriceBefDi, Price, Quantity) VALUES
(1, 100.0, 98.35, 12),
(1, 50.0, 50.0, 120),
(1, 1000.0, 20.0, 1)
--no order lines for order 2
GO
This is the query to get the total discount for all the orders with an orderDate in the current year.
;WITH CTE AS (
SELECT ORDR.DocEntry
, SUM(Price * Quantity) as OrderPrice
, SUM((PriceBefDi - Price) * Quantity) AS OrderDiscount
FROM dbo.ORDR as ORDR
INNER JOIN dbo.RDR1 as RDR1
ON ORDR.DocEntry = RDR1.DocEntry
WHERE YEAR(ORDR.OrderDate) = 2020
GROUP BY ORDR.DocEntry
)
SELECT ORDR.DocEntry
, ORDR.DocNum
, ORDR.OrderDate
, COALESCE(CTE.OrderPrice, 0.0) as OrderPrice
, COALESCE(CTE.OrderDiscount, 0.0) as OrderDiscount
FROM dbo.ORDR as ORDR
LEFT OUTER JOIN CTE
ON ORDR.DocEntry = CTE.DocEntry