CODEWITHSUNDEEP
sqlsql-server-2008t-sql
wootscootinboogie
wootscootinboogie

Reputation: 8695

Get each patients' highest bill

I have a table where an ID can be associated with more than one bill. What I need to do is find the MAX billing amount, the ID and the date of their highest (MAX) bill. The problem is that there can be thousands of billsper person, and hundreds on any given date.

My query 

select patientID, max(amountPaid) as maxPaid
from myTable
group by patientID

gives me what I need, minus the date. My attempt at fixing this is

select t.patientID, t.maxPaid, myTable.billDate
from myTable
inner join
(
select patientid, max(amountPaid) as maxPaid
from myTable
group by patientID
) as t on t.patientID=myTable.patientID and =t.maxPaid=myTable.maxPaid

The error given is invalid column name maxPaid. I tried not giving the calculated field an alias but SQL Server wouldn't accept myTable.max(amountPaid) either. What's the quickest way to fix this? thanks in advance.

Upvotes: 1

Views: 1051

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

;WITH x AS (SELECT PatientID, BillDate, AmountPaid,
  rn = ROW_NUMBER() OVER (PARTITION BY PatientID ORDER BY AmountPaid DESC)
  FROM dbo.myTable
)
SELECT PatientID, BillDate, AmountPaid
FROM x
WHERE rn = 1;

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 838276

The problem with your current approach is that if a patient has two bills with the maximum amount, you will get both of them.

Try this instead:

SELECT 
    patientid,
    amountPaid AS max_paid,
    billDate
FROM
(
    SELECT
        patientid,
        amountPaid,
        billDate,
        ROW_NUMBER() OVER (PARTITION BY patientid
                           ORDER BY amountpaid DESC) AS RowNumber
    FROM myTable 
) T1
WHERE T1.RowNumber = 1

This will always return one row per patient even if a patient has two bills that both have the same maximum amountpaid.

Upvotes: 4

Taryn
Taryn

Reputation: 247710

Based on you description, I think you meant this:

select t1.patientID, t2.maxPaid, t1.billDate
from myTable t1
inner join
(
    select patientid, max(amountPaid) as maxPaid
    from myTable
    group by patientID
) t2
on t1.patientID=t2.patientID 
and t1.amountPaid=t2.maxPaid

Upvotes: 1

Related Questions