(CASE WHEN COALESCE(discountData.discountTotal, 0) != 0 THEN (COALESCE(transaction.total, 0) – COALESCE(transaction.taxtotal, 0) + (COALESCE(discountData.discountTotal, 0) – COALESCE(discountItemData.discountItem, 0))) ELSE (COALESCE(transaction.total, 0) – COALESCE(transaction.taxtotal, 0)) END) AS invoiceSubtotal
LEFT JOIN (SELECT transactionLine.transaction, SUM(ABS(transactionLine.amount)) AS discountTotal FROM transactionLine WHERE transactionLine.itemtype = ‘Discount’ GROUP BY transactionLine.transaction) discountData ON transaction.id = discountData.transaction LEFT JOIN (SELECT transactionLine.transaction, SUM(transactionLine.netamount) AS OtherCharges FROM transactionLine WHERE transactionLine.itemtype = ‘OthCharge’ GROUP BY transactionLine.transaction) discountItemData ON transaction.id = discountItemData.transaction LEFT JOIN transactionLine transactionLine ON transaction.id = transactionLine.transaction