The code below works like expected. The problem comes with the occasional invoice that has two lines with different ADDIT_CD_LINEs. The report totals for MER_AMT, DISC_AMT and NETSALES a doubled for that line.
I need to divide the totals for that two line invoice by two, otherwise total as is.
I'm thinking a count() function along with an IIF() but not sure how to code it.
SELECT
AR.ORDER_NO,
AR.INVOICE_NBR,
AR.CUST_NBR,
COALESCE(AR_MERCH_TOTALS.MER_AMT,0) AS MER_AMT,
COALESCE(AR_DISC_TOTALS.DISC_AMT,0) AS DISC_AMT,
(COALESCE(AR_MERCH_TOTALS.MER_AMT,0) - COALESCE(AR_DISC_TOTALS.DISC_AMT,0)) AS NETSALES,
COALESCE(AR_ADD_TOTALS.ADD_AMT,0) AS ADD_AMT,
AR_ADD_TOTALS.ADDIT_CD_LINE,
COALESCE(AR_FRT_TOTALS.FRT_AMT,0) AS FRT_AMT,
AR.INV_DATE
FROM ARIV AR
LEFT JOIN (SELECT AR1.U2_ID,AR1.ORDER_NO, SUM(ARDISC1.DISC_AMT) AS DISC_AMT
FROM ARIV AR1
LEFT JOIN ARIV_DISC_DETAIL11 ARDISC1 ON AR1.U2_ID = ARDISC1.u2_id
WHERE AR1.INV_DATE BETWEEN '07/28/25' AND '07/28/25'
GROUP BY AR1.U2_ID,AR1.ORDER_NO) AR_DISC_TOTALS ON AR.u2_id = AR_DISC_TOTALS.u2_id
LEFT JOIN (SELECT AR1.U2_ID,AR1.ORDER_NO, SUM(ARMERCH.MER_AMT) AS MER_AMT
FROM ARIV AR1
LEFT JOIN ARIV_MER_DETAIL11 ARMERCH ON AR1.U2_ID = ARMERCH.u2_id
WHERE AR1.INV_DATE BETWEEN '07/28/25' AND '07/28/25'
GROUP BY AR1.U2_ID,AR1.ORDER_NO) AR_MERCH_TOTALS ON AR.U2_ID = AR_MERCH_TOTALS.u2_id
LEFT JOIN (SELECT AR1.U2_ID, AR1.ORDER_NO, SUM(ARADD.ADDIT_AMT) AS ADD_AMT, ARADD.ADDIT_CD_LINE
FROM ARIV AR1
LEFT JOIN ARIV_ADDL_DETAIL11 ARADD ON AR1.U2_ID = ARADD.u2_id
WHERE AR1.INV_DATE BETWEEN '07/28/25' AND '07/28/25'
GROUP BY AR1.U2_ID, AR1.ORDER_NO,ARADD.ADDIT_CD_LINE) AR_ADD_TOTALS ON AR.U2_ID = AR_ADD_TOTALS.u2_id
LEFT JOIN (SELECT AR1.U2_ID,AR1.ORDER_NO, SUM(ARFRT.FRT_AMT) AS FRT_AMT
FROM ARIV AR1
LEFT JOIN ARIV_FRT_DETAIL11 ARFRT ON AR1.U2_ID = ARFRT.u2_id
WHERE AR1.INV_DATE BETWEEN '07/28/25' AND '07/28/25'
GROUP BY AR1.U2_ID, AR1.ORDER_NO, AR1.INVOICE_NBR) AR_FRT_TOTALS ON AR.u2_id = AR_FRT_TOTALS.u2_id
WHERE AR.INV_DATE BETWEEN '07/28/25' AND '07/28/25' and (AR.INVOICE_NBR NOT LIKE 'M*') AND (AR.ORDER_NO <> '') AND (COALESCE(AR_MERCH_TOTALS.MER_AMT,0) - COALESCE(AR_DISC_TOTALS.DISC_AMT,0)) >= 0
ORDER BY AR.ORDER_NO