CODEWITHSUNDEEP
mysql
Saseow
Saseow

Reputation: 23

Mysql query for payment

I have a table called logs that holds jobs that a technician does for a doctor by date. It contains fields: ref_doctor_id (doctor), technician_id (technician) and dos (date).
The technician may do jobs for several doctors on a specific date. For each doctor I need to find out if the technician did jobs for any other doctors on a specific day and if he did, I need to know how many doctors he did jobs for on that day. This is to assign costs to doctors.

For example:
I am working on Doctor 6 (id=6) and the technician gets $100 per day.
On 5th he works only for doctor 6 so the cost for doctor 6 is $100.
On 6th he works for doctors 6 and 8 so the cost for doctor six would be $50.
On 7th he works for doctor 6, 7 and 8 so the cost for doctor 6 would be $33.33.
The total cost for the doctor for the period is $183.33.

I hope this is clear enough and I am hoping that SQL can solve this.
Thank you in advance!

2013-03-05  6   16
2013-03-06  6   16
2013-03-06  8   16
2013-03-07  6   16
2013-03-07  8   16
2013-03-07  10  16

The option I have is to select the records that contain the doctor's ID and the technicians ID then, using PHP loop through the recordset dates to see if there are any other doctors that were worked on for that date by that technician. This just seems to be a long way round but may be the only solution.

Upvotes: 0

Views: 72

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269723

Calculate the number of doctors in a subquery and join the results back in:

select dos, technician_id, dt.numdocs
from t left outer join
     (select dos, technician_id, count(distinct ref_doctor_id) as numdocs
      from t
      group by dos, technician_id
     ) dt
     on dt.dos = t.dos and dt.technician_id = t.technician_id
where ref_doctor_id = 6
group by dos, technician_id

Upvotes: 1

Related Questions