;WITH Orders AS
(
SELECT
cOrderId,
kUser,
fItemPromotionDiscount,
fShipPromotionDiscount,
dErstellt,
WawiDaten.kBestellung,
WawiDaten.cBestellNr,
WawiDaten.dBestellungErstellt,
WawiDaten.kRechnung,
WawiDaten.cRechnungsNr,
WawiDaten.dRechnungErstellt,
WawiDaten.nBestellungStorno,
WawiDaten.kGutschrift,
WawiDaten.nIstVcs
FROM (
SELECT
pf_amazon_bestellung.cOriginalOrderId AS cOrderId,
pf_amazon_bestellung.kUser,
SUM(fItemPromotionDiscount) AS fItemPromotionDiscount,
SUM(fShipPromotionDiscount) AS fShipPromotionDiscount,
MAX(dErstellt) AS dErstellt
FROM
(
SELECT
cOriginalOrderId,
kUser
FROM dbo.pf_amazon_bestellung
WHERE pf_amazon_bestellung.dErstellt > '2021-11-11' AND pf_amazon_bestellung.dErstellt < '2022-01-10'
GROUP BY kUser, cOriginalOrderId
) AS Sub
JOIN pf_amazon_bestellung ON pf_amazon_bestellung.kUser = Sub.kUser
AND pf_amazon_bestellung.cOriginalOrderId = Sub.cOriginalOrderId
GROUP BY pf_amazon_bestellung.kUser, pf_amazon_bestellung.cOriginalOrderId
) AS AmazonOrders
OUTER APPLY (
SELECT TOP(1) tbestellung.kBestellung, tbestellung.cBestellNr, tbestellung.dErstellt, tbestellung.nStorno, trechnung.kRechnung, trechnung.cRechnungsNr, trechnung.dErstellt, tgutschrift.kGutschrift,
CASE WHEN tBestellung.cType IN ('V', 'W') THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS nIstVcs
FROM dbo.pf_amazon_bestellung AS ab
JOIN dbo.pf_amazon_bestellungpos ON pf_amazon_bestellungpos.kAmazonBestellung = ab.kAmazonBestellung
JOIN dbo.tbestellpos ON tbestellpos.kAmazonBestellungPos = pf_amazon_bestellungpos.kAmazonBestellungPos
JOIN dbo.tbestellung ON tbestellung.kBestellung = tbestellpos.tBestellung_kBestellung
LEFT JOIN dbo.trechnung ON trechnung.kRechnung = tbestellung.tRechnung_kRechnung
LEFT JOIN dbo.tgutschrift ON tgutschrift.kRechnung = tbestellung.tRechnung_kRechnung
WHERE ab.cOriginalOrderId = AmazonOrders.cOrderId
ORDER BY tbestellung.nStorno DESC, tgutschrift.kGutschrift DESC, trechnung.dErstellt DESC, tbestellung.dErstellt DESC) AS WawiDaten(kBestellung, cBestellNr, dBestellungErstellt, nBestellungStorno, kRechnung, cRechnungsNr, dRechnungErstellt, kGutschrift, nIstVcs)
), Settlements AS
(
SELECT
OrderID AS cOrderId,
kUser,
SUM(CASE WHEN pf_amazon_settlementpos.AmountType = 'Promotion' AND AmountDescription NOT IN ('
Shipping', 'TaxDiscount') THEN Amount ELSE 0.0 END) AS fItemPromotionDiscount,
SUM(CASE WHEN pf_amazon_settlementpos.AmountType = 'Promotion' AND AmountDescription IN ('Shipping', 'TaxDiscount') THEN Amount ELSE 0.0 END) AS fShipPromotionDiscount
FROM dbo.pf_amazon_settlementpos
WHERE pf_amazon_settlementpos.TransactionType = 'Order'
GROUP BY kUser, OrderID
)
SELECT
Orders.cOrderId,
Orders.kUser,
Orders.dErstellt,
Orders.fItemPromotionDiscount AS fOrderItemPromotionDiscount,
Orders.fShipPromotionDiscount AS fOrderShipPromotionDiscount,
Settlements.fItemPromotionDiscount AS fSettlementItemPromotionDiscount,
Settlements.fShipPromotionDiscount AS fSettlementShipPromotionDiscount,
Settlements.fItemPromotionDiscount - Orders.fItemPromotionDiscount AS fFixUpItemPromotionDiscount,
Settlements.fShipPromotionDiscount - Orders.fShipPromotionDiscount AS fFixUpShipPromotionDiscount,
Orders.kBestellung,
Orders.cBestellNr,
Orders.dBestellungErstellt,
Orders.kRechnung,
Orders.cRechnungsNr,
Orders.dRechnungErstellt,
Orders.nBestellungStorno,
Orders.kGutschrift,
Orders.nIstVcs
FROM Orders
LEFT JOIN Settlements ON Settlements.cOrderId = Orders.cOrderId AND Settlements.kUser = Orders.kUser
WHERE ISNULL(Orders.fItemPromotionDiscount, 0.0) <> ISNULL(Settlements.fItemPromotionDiscount, 0.0) OR ISNULL(Orders.fShipPromotionDiscount, 0.0) <> ISNULL(Settlements.fShipPromotionDiscount, 0.0)
ORDER BY Orders.dErstellt, Orders.cOrderId, Settlements.cOrderID;