SQL sum the ledger per last person to do service 1

[opticalman]

I have 2 tables. One is a ledger of payments made per patient ID#. The other is services done for the patient with the doctors name and date of service. I need to sum up the ledger payments and assoiciate those payments with the last doctor to do a service.(the last doctor to see the patient gets credit for any payments) Any suggestions?

ledger
idnumber date paid
0001 01/22/2003 $10
0001 03/24/2004 $20
0002 05/26/2005 $30
0002 07/28/2006 $40

services
idnumber date doctor service
0001 01/02/2003 jones office visit
0001 03/04/2004 smith office visit
0002 05/06/2005 smith office visit
0002 07/05/2006 jones office visit

thanks in advance
Jim Rumbaugh

 

[bborissov]

And if you post what you want as a result from these tables it will be easier to me to understand what you want

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

SQL Server 2005 solution and up

select * from (select sum(L.Paid) over (partition by L.IdNumber) as TotalPaid, S.Date, S.Doctor, S.Service, S.IdNumber, Row_Number() over (partition by IDNumber order by S.Date DESC) as RowNum from Services S INNER JOIN Ledger L on S.IdNumber = L.IdNumber) X where RowNum = 1

From the top of my head.

 

[markros]

Typo fix

select * from (select sum(L.Paid) over (partition by L.IdNumber) as TotalPaid, S.Date, S.Doctor, S.Service, S.IdNumber, Row_Number() over (partition by S.IDNumber order by S.Date DESC) as RowNum from Services S INNER JOIN Ledger L on S.IdNumber = L.IdNumber) X where RowNum = 1

 

[opticalman]

Thanks Borislav

Here is the rule I am trying calculate. "Once a doctor sees a patient, all future payments are credited to that doctor."

I need to group the exams by patient's IDNUMBER and determine who was the last doctor. Then add the payments from those patients to see what money is comeing from what doc. What I want is:

DOCTOR ALLPAID
Jones $70
Smith $30

Jones last saw patient 0002, therefore gets all that 0002 paid
Smith last saw patient 0001, therefore gets all that 0001 paid

MARKROS
what you wrote comes close. Your code did work for the example that I showed, which was only 2 patients and 2 doctors, but I actually have over 100 patients. What I recieveed was over 100 totals. This is getting close to what I need. I also see that I need to learn about "PARTINION BY" and "OVER".

 

[markros]

I see nothing wrong with the solution I posted - it should have gave you the correct result.

 

[gmmastros]

It sounds like you want a single row for each doctor showing the total amount. If that's the case, you need to group differently.

Select PatientsForDoctor.Doctor,
       Sum(TotalsByPatient.TotalPerPatient) As TotalPerDoctor
From   (
       Select IdNumber, Sum(Paid) As TotalPerPatient
       From   ledger
       Group By IdNumber
       ) As TotalsByPatient
       Inner Join (
         Select S.IdNumber, S.Doctor
         From   Services S
                Inner Join (
                  Select IdNumber, Max(Date) As MaxDate
                  From   services
                  Group By IdNumber
                  ) As MaxStuff
                  On  S.IdNumber = MaxStuff.IdNumber
                  And S.Date = MaxStuff.MaxDate
         ) As PatientsForDoctor
         On TotalsByPatient.IdNumber = PatientsForDoctor.IdNumber
Group By PatientsForDoctor.Doctor

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I see, you wanted summary per doctor, in this case using the solution I already posted

select Doctor, sum(TotalPaid) as AllPaid from (select sum(L.Paid) over (partition by L.IdNumber) as TotalPaid, S.Date, S.Doctor, S.Service, S.IdNumber, Row_Number() over (partition by S.IDNumber order by S.Date DESC) as RowNum from Services S INNER JOIN Ledger L on S.IdNumber = L.IdNumber) X where RowNum = 1) AllPatientsSummary group by Doctor

 

[markros]

Or to simplify a bit

;with cte_PatientsSummary as (select sum(L.Paid) over (partition by L.IdNumber) as TotalPaid, S.Date, S.Doctor, S.Service, S.IdNumber, Row_Number() over (partition by S.IDNumber order by S.Date DESC) as RowNum from Services S INNER JOIN Ledger L on S.IdNumber = L.IdNumber) X where RowNum = 1)

select Doctor, sum(TotalPaid) as AllPaid from cte_PatientSummary group by Doctor

 

[gmmastros]

that's not simpler. not to me, anyway.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Imho, it's simpler than multiple JOINs with derived tables.

 

[opticalman]

THANK YOU to Markros and Gmmastros for your help.

Markros: there is a missing "(" somewhere in your second group of suggested code. I never got it running. I still need to learn how to use "partition by"

Gmastros: I apreciate the readablity of your code via use of space. It almost did the job as posted.

Here is the final code I have put into production.

Select PatientsForDoctor.Doctor,
Sum(TotalsByPatient.TotalPerPatient) As TotalPerDoctor
From (
Select IdNumber, Paid As TotalPerPatient
From ledgerDr
) As TotalsByPatient
left Join
(
SELECT DISTINCT S.IdNumber, S.Doctor
From Exams S
inner Join (
Select IdNumber, Max(Date1) As MaxDate
From exams
Group By IdNumber
) As MaxStuff
On S.IdNumber = MaxStuff.IdNumber
AND s.date1 = MaxStuff.MaxDate
) As PatientsForDoctor
On TotalsByPatient.IdNumber = PatientsForDoctor.IdNumber
Group By PatientsForDoctor.Doctor


The 2 edits I had to make/add.
1)The first JOIN was changed from INNER to LEFT. The report picked up additional ledger entries that had NO doctor assaciated to them
2)Channged the second SELECT to SELECT DISTINCT. If two services were entered on the last day, it returned 2 references to that day, resulting in double the payments reported.

I consider this problem solved. ( and I have learned a lot)

 

[markros]

Ouch

The correct code should have been

;with cte_PatientsSummary as ([b]select * from [/b](select sum(L.Paid) over (partition by L.IdNumber) as TotalPaid, S.Date, S.Doctor, S.Service, S.IdNumber, Row_Number() over (partition by S.IDNumber order by S.Date DESC) as RowNum from Services S INNER JOIN Ledger L on S.IdNumber = L.IdNumber) X where RowNum = 1)

select Doctor, sum(TotalPaid) as AllPaid from cte_PatientSummary group by Doctor

missed the bolded part.