Last post Dec 19, 2014 02:59 AM by spenzo344
Dec 19, 2014 01:13 AM|Junior1012|LINK
Please, can anyone help me out or give me idea how to get these two records combination with fees combination as well except for doctor fees is only one time charges.
I have one appointment made by one patient but he has two records coz he did two Diagnosis which is X-ray and Ultrasound
I got these two records like following
AppointmentId AppointmentCode DiagnosisRepType docprice diaprice FirstName MiddleName LastName
2 B-20_19/12/2014_002 X-ray 8000 20000 Aye Aye Soe
2 B-20_19/12/2014_002 Ultrasound 8000 28000 Aye Aye Soe
But I want only one time for doctor fees and sum for different fees for Diagnosis in only one distinct record..like following
AppointmentId AppointmentCode docprice diaprice FirstName MiddleName LastName
2 B-20_19/12/2014_002 8000 48000 Aye Aye Soe
Here is my query:
select app.AppointmentId,app.AppointmentCode,drt.DiagnosisReportType,dp.Price as docprice,
drt.Price as diaprice,FirstName,MiddleName,LastName from Appointment app
join Patient p on p.PatientId=app.PatientId
left join DiagnosisReport dr on app.AppointmentId=dr.AppointmentId
left join DiagnosisReportType drt on dr.DiagnosisReportTypeId=drt.DiagnosisReportTypeId
join DoctorPrice dp on dp.DoctorId=app.DoctorId where app.DeleteStatus='N'
and dp.DeleteStatus='N' and PrintStatus='New' and app.AppointmentDate='19/12/2014'
Dec 19, 2014 02:55 AM|Junior1012|LINK
I got the answer now. Thanks.
Dec 19, 2014 02:59 AM|spenzo344|LINK
If Docprice is same then simple sum the diaprice group by rest of column .. it will give you desired results.
group by AppointmentId ,AppointmentCode,