bjones8888
asked on
Sql Server aggregation problem
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:
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.
any ideas?
Here's my current SQL statement:
select
Base.CASENO as [Matter Number],
INVOICES.INV_NO as [Invoice Number],
sum(INVTRANS.TRANS_AMT) as [Total Fees],
ISNULL(INVTRANS.TRANS_HOURS, '') as [Units],
CASE
WHEN INVTRANS.TRANS_HOURS IS NULL THEN 0
WHEN INVTRANS.TRANS_HOURS = 0 THEN 0
ELSE INVTRANS.TRANS_AMT/INVTRANS.TRANS_HOURS
END as [Rate],
INVTRANS.TRANS_AMT as [Item Total],
INVTRANS.INVTRANS_ID
from INVTRANS inner join INVOICES on INVTRANS.INVOICE_ID = INVOICES.INVOICE_ID
left outer join BASE on INVTRANS.MATTER_ID = BASE.MATTER_ID
group by
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.
any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
:) good guess then (kidding)
thanks
Cheers, Paul
thanks
Cheers, Paul
ASKER
Thank you.