SET @date_type = 'booking_date';

SELECT 
vw_pd.patient_unique_id,
vw_pd.patient_name,
oda.appointment_no,
oda.booking_date,
oda.doctor_appointment_date,
docs.*,
oda.doctor_fees,
psc.net_amount,
psc.due_amount,
oda.appointment_status
FROM 
opd_doctor_appointments as oda
INNER JOIN vw_pharmacy_patient as vw_pd ON vw_pd.id = oda.patient_id
INNER JOIN (SELECT doc.id, doc.doctor_name, doc_sp.speciality_name FROM doctors as doc INNER JOIN doctor_speciality as doc_sp ON doc_sp.id = doc.specialty_id) as docs ON docs.id = oda.doctor_id
INNER JOIN patient_service_charges as psc ON psc.billing_module_id = 3 AND psc.service_source_id = oda.id
WHERE DATE(CASE WHEN @date_type='app_date' THEN oda.doctor_appointment_date ELSE oda.booking_date END) >= '2022-02-14' AND DATE(CASE WHEN @date_type='app_date' THEN oda.doctor_appointment_date ELSE oda.booking_date END) <= '2022-02-15'
ORDER BY oda.doctor_appointment_date DESC
