I have an interesting problem. I have to present data with a single sql statement from invoice header and invoice transaction tables.
Here's my current SQL statement:
Base.CASENO as [Matter Number],
INVOICES.INV_NO as [Invoice Number],
sum(INVTRANS.TRANS_AMT) as [Total Fees],
ISNULL(INVTRANS.TRANS_HOURS, '') as [Units],
WHEN INVTRANS.TRANS_HOURS IS NULL THEN 0
WHEN INVTRANS.TRANS_HOURS = 0 THEN 0
END as [Rate],
INVTRANS.TRANS_AMT as [Item Total],
from INVTRANS inner join INVOICES on INVTRANS.INVOICE_ID = INVOICES.INVOICE_ID
left outer join BASE on INVTRANS.MATTER_ID = BASE.MATTER_ID
base.CASENO, invoices.INV_NO, INVTRANS.TRANS_HOURS, INVTRANS.TRANS_AMT, INVTRANS.INVTRANS_ID
order by 2, 1
Here's the result I get from my data:
The problem is that the Total Fees column should be the sum of that column, where the invoice number and matter number are the same, redundantly shown for each row. For example, in my image above, rows 5 & 6 are the same invoice number and matter number, and so each should show 60.00 (the total of Item Total for both rows). And rows 8 and 9 should each have 150.00 as Total Fees, since they have the same invoice number and matter number.
Normally, this would be the invoice header record's total, but in this instance, my client needs the equivalent of a subtotal by matter number within the invoice numbers.