extract_akhil.sql

-- Extract normal results
select preg.first_name,ios.Service_Name, inr.result,ulm.Unit_name, inr.min_value, inr.max_value, invsam.lab_regno
from patient_registration preg inner join (investigation_sample_op invsam
inner join (investigation_normal_result_op inr 
inner join item_of_service ios 
on ((ios.Service_code=inr.service_code) and (ios.Report_Type='N')))
on ((inr.service_code = invsam.service_code) 
     and (inr.lab_regno = invsam.lab_regno)))
on preg.registration_no = invsam.Registration_No inner join Unit_Lab_Master ulm on ulm.unit_code=inr.Unit_code
where preg.registration_no='10712'


-- Extract Textual Results

select preg.first_name,preg.family_name,
ios.Service_Name, ios.Service_code, ios.Report_Type, 
invsam.lab_regno, preg.registration_no, text_report = case ios.Report_Type
	WHEN 'T' then (select itr.formatstr from investigation_typical_result_op itr where lab_regno=invsam.lab_regno and itr.service_code=invsam.service_code)
	WHEN 'F' then (select itr.formatstr from investigation_typical_result_op itr where lab_regno=invsam.lab_regno and itr.service_code=invsam.service_code)
	ELSE 'NA'
	END
from investigation_sample_op invsam inner join patient_registration preg on invsam.Registration_No = preg.registration_no
inner join item_of_service ios on invsam.service_code = ios.Service_code
where invsam.Registration_No='10712' and (ios.Report_Type='T' or ios.Report_Type='F') order by invsam.lab_regno