Adding a count function

SSAS and SSRS
1

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
2

You'll probably have to group by AR.ORDER_NO,
AR.INVOICE_NBR,
AR.CUST_NBR,
AR_ADD_TOTALS.ADDIT_CD_LINE,
AR.INV_DATE

and then all the coalesce statements, you can add an Average to it

3

I'm not 100% sure I understand, but I think you mean this!?


SELECT
    ...
	COALESCE(AR_ADD_TOTALS.ADD_AMT,0) / CASE WHEN AR_FRT_TOTALS.FRT_AMT IS NULL 
        THEN 1 ELSE 2 END AS ADD_AMT,
	AR_ADD_TOTALS.ADDIT_CD_LINE,
	COALESCE(AR_FRT_TOTALS.FRT_AMT,0) / CASE WHEN AR_ADD_TOTALS.ADD_AMT IS NULL 
        THEN 1 ELSE 2 END AS FRT_AMT,
	AR.INV_DATE
    FROM ...
4

Sorry it's been a bit. I finished a large project over the weekend, finally finishing yesterday.
I'll look into both of these and see which one I think will work better (and easier).

5

If there can be two different ADDIT_CD_LINEs, does that mean there could possibly be more than two?