Hi All
SQL query 1: this query gives me correct results only but it is taking time to execute the query
SELECT
pat.PatientID,
(SELECT Top 1 InsuranceID FROM ActivePatientLocInsurance WHERE FK_PatientID=pat.PatientID ORDER BY StartDate DESC) AS InsuranceID,
(SELECT Top 1 InsuranceName FROM ActivePatientLocInsurance WHERE FK_PatientID=pat.PatientID ORDER BY StartDate DESC) AS InsuranceName,
(SELECT Top 1 PolicyNumber FROM ActivePatientLocInsurance WHERE FK_PatientID=pat.PatientID ORDER BY StartDate DESC) AS PolicyNumber,
(SELECT Top 1 AuthorizationNumber FROM ActivePatientLocInsurance WHERE FK_PatientID=pat.PatientID ORDER BY StartDate DESC) AS AuthorizationNumber
FROM PatientDetails pat
SQL Query 2: this query execting faster than the first query but I am getting duplicate values. Is it possible to add order by with Left Join
SELECT
pat.PatientID,
ins.InsuranceID,
ins.InsuranceName,
ins.PolicyNumber,
ins.AuthorizationNumber
FROM PatientDetails pat
LEFT JOIN ActivePatientLocInsurance ins on pat.PatientID = ins.FK_PatientID
Thanks
Babu