SQL - Sub Query (Select Top 1) Help Needed
Please note, the SQL is handled dynamically by the SQL server, therefore some of the coding will look alittle odd - please ignore.
What I need assistance with is this section:
'Contact1First' = IsNull(pc.First,''),
'Contact1Middle' = IsNull(pc.Middle,''),
'Contact1Last' = IsNull(pc.Last,'')
A patient can have several contacts, I just need the top 1. Any help is appreciated.
What I need assistance with is this section:
'Contact1First' = IsNull(pc.First,''),
'Contact1Middle' = IsNull(pc.Middle,''),
'Contact1Last' = IsNull(pc.Last,'')
A patient can have several contacts, I just need the top 1. Any help is appreciated.
SET NOCOUNT ON
SELECT
'PatientName' = ISNULL(pp.First , '') + ' ' + ISNULL(pp.Middle , '') + ' ' + ISNULL(pp.Last , '') + ' ' + ISNULL(pp.Suffix , ''),
'PatLast' = ISNULL(pp.Last , '') ,
'PatFirst' = ISNULL(pp.First , '') ,
'PatMiddle' = ISNULL(pp.Middle , '') ,
'PatientAddr1' = ISNULL(pp.Address1 , '') ,
'PatientAddr2' = ISNULL(pp.Address2 , '') ,
'PatientCity' = ISNULL(pp.City , '') ,
'PatientState' = ISNULL(pp.State , '') ,
'PatientZip' = ISNULL(pp.Zip , '') ,
'PatientCountry' = ISNULL(pp.Country , '') ,
'PatientBirthdate' = pp.Birthdate ,
'PatientSex' = CASE WHEN pp.Sex = 'M' THEN 'Male'
WHEN pp.Sex = 'F' THEN 'Female'
ELSE ISNULL(pp.Sex , '')
END ,
'PatientPhone1' = ISNULL(pp.Phone1 , '') ,
'PatientPhone1Type' = ISNULL(pp.Phone1Type , '') ,
'PatientPhone2' = ISNULL(pp.Phone2 , '') ,
'PatientPhone2Type' = ISNULL(pp.Phone2Type , '') ,
'PatientSSN' = ISNULL(SUBSTRING(pp.SSN , 1 , 3) + '-' + SUBSTRING(pp.SSN , 4 , 2) + '-' + SUBSTRING(pp.SSN , 6 , 4) , ''),
'GuarantorName' = ISNULL(g.First , '') + ' ' + ISNULL(g.Middle , '') + ' ' + ISNULL(g.Last , '') ,
'GuarantorAddr1' = ISNULL(g.Address1 , '') ,
'GuarantorAddr2' = ISNULL(g.Address2 , '') ,
'GuarantorCity' = ISNULL(g.City , '') ,
'GuarantorState' = ISNULL(g.State , '') ,
'GuarantorZip' = ISNULL(g.Zip , '') ,
'GuarantorCountry' = ISNULL(g.Country , '') ,
'GuarantorPhone1' = ISNULL(g.Phone1 , '') ,
'GuarantorPhone1Type' = ISNULL(g.Phone1Type , '') ,
'GuarantorPhone2' = ISNULL(g.Phone2 , '') ,
'GuarantorPhone2Type' = ISNULL(g.Phone2Type , '') ,
'GuarantorBirthdate' = g.birthdate ,
'GuarantorSSN' = ISNULL(SUBSTRING(g.SSN , 1 , 3) + '-' + SUBSTRING(g.SSN , 4 , 2) + '-' + SUBSTRING(g.SSN , 6 , 4) , '') ,
'Doctor' = ISNULL(df.First , '') + ' ' + ISNULL(df.Middle , '') + ' ' + ISNULL(df.Last , '') + ' ' + ISNULL(df.Suffix , '') ,
'DoctorFirst' = ISNULL(df.First , '') ,
'DoctorMiddle' = ISNULL(df.Middle , '') ,
'DoctorLast' = ISNULL(df.Last , '') ,
'DoctorSuffix' = ISNULL(df.Suffix , ''),
'AppointmentsID' = a.AppointmentsID ,
'ApptStart' = a.ApptStart ,
'StartHour' = ISNULL(CONVERT(VARCHAR(2) , DATEPART(hour , a.ApptStart)) , '') ,
'StartMinute' = ISNULL(CONVERT(VARCHAR(2) , DATEPART(minute , a.ApptStart)) , '') ,
'StopHour' = ISNULL(CONVERT(VARCHAR(2) , DATEPART(hour , a.ApptStop)) , '') ,
'StopMinute' = ISNULL(CONVERT(VARCHAR(2) , DATEPART(minute , a.ApptStop)) , '') ,
'ApptTime' = DATEDIFF(minute , a.ApptStart , a.ApptStop) ,
'AppointmentType' = ISNULL(at.Name , ''),
'ApptNotes' = ISNULL(CONVERT(VARCHAR(255) , a.Notes) , '') ,
'Carrier1Name' = ISNULL(ic.Name , '') ,
'Carrier1ListName' = ISNULL(ic.Listname , '') ,
'Carrier1Address1' = ISNULL(ic.Address1 , '') ,
'Carrier1Address2' = ISNULL(ic.Address2 , '') ,
'Carrier1City' = ISNULL(ic.City , '') ,
'Carrier1State' = ISNULL(ic.State , '') ,
'Carrier1Zip' = ISNULL(ic.Zip , '') ,
'Carrier1Country' = ISNULL(ic.Country , '') ,
'Carrier1Contact' = ISNULL(ic.Contact , '') ,
'Carrier1Phone' = ISNULL(dbo.formatphone(ic.phone1 , 1) , '') ,
'Carrier1FinancialClass' = ISNULL(ml.Description , '') ,
'Carrier1AllocationType' = ISNULL(al.Name , '') ,
'Carrier1CarrierType' = ISNULL(ml3.Description , '') ,
'Carrier1PolicyType' = ISNULL(ml4.Description , '') ,
'Carrier1GroupId' = ISNULL(ic.GroupId , '') ,
'Carrier1GroupName' = ISNULL(ic.GroupName , '') ,
'Insured1SameAsPatient' = ISNULL(pi.InsuredSameAsPatient , 0) ,
'Insured1SameAsGuarantor' = ISNULL(pi.InsuredSameAsGuarantor , 0) ,
'Insured1PatRelToInsured' = CASE WHEN COALESCE(pi.InsuredSameAsPatient , 1) = 1 THEN 'Same as Patient'
WHEN COALESCE(pi.InsuredSameAsGuarantor , 1) = 1 THEN 'Same as Guarantor'
ELSE ISNULL(ml2.Description , '')
END ,
'Insured1First' = CASE WHEN COALESCE(pi.InsuredSameAsPatient , 1) = 1 THEN pp.First
WHEN COALESCE(pi.InsuredSameAsGuarantor , 1) = 1 THEN g.first
ELSE ISNULL(pi.First , '')
END ,
'Insured1Last' = CASE WHEN COALESCE(pi.InsuredSameAsPatient , 1) = 1 THEN pp.Last
WHEN COALESCE(pi.InsuredSameAsGuarantor , 1) = 1 THEN g.Last
ELSE ISNULL(pi.Last , '')
END ,
'Insured1Middle' = CASE WHEN COALESCE(pi.InsuredSameAsPatient , 1) = 1 THEN pp.middle
WHEN COALESCE(pi.InsuredSameAsGuarantor , 1) = 1 THEN g.middle
ELSE ISNULL(pi.middle , '')
END ,
'Insured1Address1' = CASE WHEN COALESCE(pi.InsuredSameAsPatient , 1) = 1 THEN pp.Address1
WHEN COALESCE(pi.InsuredSameAsGuarantor , 1) = 1 THEN g.Address1
ELSE ISNULL(pi.Address1 , '')
END ,
'Insured1Address2' = CASE WHEN COALESCE(pi.InsuredSameAsPatient , 1) = 1 THEN pp.Address2
WHEN COALESCE(pi.InsuredSameAsGuarantor , 1) = 1 THEN g.Address2
ELSE ISNULL(pi.Address2 , '')
END ,
'Insured1City' = CASE WHEN COALESCE(pi.InsuredSameAsPatient , 1) = 1 THEN pp.city
WHEN COALESCE(pi.InsuredSameAsGuarantor , 1) = 1 THEN g.city
ELSE ISNULL(pi.city , '')
END ,
'Insured1State' = CASE WHEN COALESCE(pi.InsuredSameAsPatient , 1) = 1 THEN pp.state
WHEN COALESCE(pi.InsuredSameAsGuarantor , 1) = 1 THEN g.state
ELSE ISNULL(pi.state , '')
END ,
'Insured1Zip' = CASE WHEN COALESCE(pi.InsuredSameAsPatient , 1) = 1 THEN pp.zip
WHEN COALESCE(pi.InsuredSameAsGuarantor , 1) = 1 THEN g.zip
ELSE ISNULL(pi.zip , '')
END ,
'Insured1Country' = ISNULL(ic.Country , '') ,
'Insured1Phone' = CASE WHEN COALESCE(pi.InsuredSameAsPatient , 1) = 1 THEN ISNULL(dbo.formatphone(pp.phone1 , 1) , '')
WHEN COALESCE(pi.InsuredSameAsGuarantor , 1) = 1 THEN ISNULL(dbo.formatphone(g.phone1 , 1) , '')
ELSE ISNULL(dbo.formatphone(pi.phone1 , 1) , '')
END ,
'Insured1ID' = ISNULL(pi.InsuredID , '') ,
'Insured1Group' = ISNULL(PI.groupid , '') ,
'Ins1_EffDate' = CASE WHEN pi.InsCardEffectiveDate IS NULL THEN ''
ELSE CONVERT(VARCHAR , pi.InsCardEffectiveDate , 101)
END ,
'Insured1SSN' = CASE WHEN COALESCE(pi.InsuredSameAsPatient , 1) = 1
THEN ISNULL(SUBSTRING(pp.SSN , 1 , 3) + '-' + SUBSTRING(pp.SSN , 4 , 2) + '-' + SUBSTRING(pp.SSN , 6 , 4) , '')
WHEN COALESCE(pi.InsuredSameAsGuarantor , 1) = 1
THEN ISNULL(SUBSTRING(g.SSN , 1 , 3) + '-' + SUBSTRING(g.SSN , 4 , 2) + '-' + SUBSTRING(g.SSN , 6 , 4) , '')
ELSE ISNULL(SUBSTRING(pi.SSN , 1 , 3) + '-' + SUBSTRING(pi.SSN , 4 , 2) + '-' + SUBSTRING(pi.SSN , 6 , 4) , '')
END ,
'Insured1DOB' = CASE WHEN COALESCE(pi.InsuredSameAsPatient , 1) = 1 THEN CONVERT(VARCHAR(10) , pp.birthdate , 101)
WHEN COALESCE(pi.InsuredSameAsGuarantor , 1) = 1 THEN CONVERT(VARCHAR(10) , g.birthdate , 101)
ELSE ISNULL(CONVERT(VARCHAR(10) , pi.birthdate , 101) , '')
END ,
'Insured1Employer' = CASE WHEN COALESCE(pi.InsuredSameAsPatient , 1) = 1 THEN ISNULL(e.Name , '')
WHEN COALESCE(pi.InsuredSameAsGuarantor , 1) = 1 THEN ISNULL(e2.Name , '')
ELSE ISNULL(e3.NAME , '')
END ,
'Insured1Occupation' = CASE WHEN COALESCE(pi.InsuredSameAsPatient , 1) = 1 THEN ISNULL(pp.EmpOccup , '')
WHEN COALESCE(pi.InsuredSameAsGuarantor , 1) = 1 THEN ISNULL(g.EmpOccup , '')
ELSE ISNULL(pi.EmpOccup , '')
END ,
'Carrier2Name' = ISNULL(ic2.Name , '') ,
'Carrier2ListName' = ISNULL(ic2.ListName , '') ,
'Carrier2Address1' = ISNULL(ic2.Address1 , '') ,
'Carrier2Address2' = ISNULL(ic2.Address2 , '') ,
'Carrier2City' = ISNULL(ic2.City , '') ,
'Carrier2State' = ISNULL(ic2.State , '') ,
'Carrier2Zip' = ISNULL(ic2.Zip , '') ,
'Carrier2Country' = ISNULL(ic2.Country , '') ,
'Carrier2Contact' = ISNULL(ic2.Contact , '') ,
'Carrier2Phone' = ISNULL(dbo.formatphone(ic2.phone1 , 1) , '') ,
'Carrier2GroupId' = ISNULL(ic2.GroupId , '') ,
'Carrier2GroupName' = ISNULL(ic2.GroupName , '') ,
'Insured2SameAsPatient' = ISNULL(pi2.InsuredSameAsPatient , 0) ,
'Insured2SameAsGuarantor' = ISNULL(pi2.InsuredSameAsGuarantor , 0) ,
'Insured2PatRelToInsured' = CASE WHEN COALESCE(pi2.InsuredSameAsPatient , 1) = 1 THEN 'Same as Patient'
WHEN COALESCE(pi2.InsuredSameAsGuarantor , 1) = 1 THEN 'Same as Guarantor'
ELSE ISNULL(ml5.Description , '')
END ,
'Insured2First' = CASE WHEN COALESCE(pi2.InsuredSameAsPatient , 1) = 1 THEN pp.First
WHEN COALESCE(pi2.InsuredSameAsGuarantor , 1) = 1 THEN g.first
ELSE ISNULL(pi2.First , '')
END ,
'Insured2Last' = CASE WHEN COALESCE(pi2.InsuredSameAsPatient , 1) = 1 THEN pp.Last
WHEN COALESCE(pi2.InsuredSameAsGuarantor , 1) = 1 THEN g.Last
ELSE ISNULL(pi2.Last , '')
END ,
'Insured2Middle' = CASE WHEN COALESCE(pi2.InsuredSameAsPatient , 1) = 1 THEN pp.middle
WHEN COALESCE(pi2.InsuredSameAsGuarantor , 1) = 1 THEN g.middle
ELSE ISNULL(pi2.middle , '')
END ,
'Insured2Address1' = CASE WHEN COALESCE(pi2.InsuredSameAsPatient , 1) = 1 THEN pp.Address1
WHEN COALESCE(pi2.InsuredSameAsGuarantor , 1) = 1 THEN g.Address1
ELSE ISNULL(pi2.Address1 , '')
END ,
'Insured2Address2' = CASE WHEN COALESCE(pi2.InsuredSameAsPatient , 1) = 1 THEN pp.Address2
WHEN COALESCE(pi2.InsuredSameAsGuarantor , 1) = 1 THEN g.Address2
ELSE ISNULL(pi2.Address2 , '')
END ,
'Insured2City' = CASE WHEN COALESCE(pi2.InsuredSameAsPatient , 1) = 1 THEN pp.city
WHEN COALESCE(pi2.InsuredSameAsGuarantor , 1) = 1 THEN g.city
ELSE ISNULL(pi2.city , '')
END ,
'Insured2State' = CASE WHEN COALESCE(pi2.InsuredSameAsPatient , 1) = 1 THEN pp.state
WHEN COALESCE(pi2.InsuredSameAsGuarantor , 1) = 1 THEN g.state
ELSE ISNULL(pi2.state , '')
END ,
'Insured2Zip' = CASE WHEN COALESCE(pi2.InsuredSameAsPatient , 1) = 1 THEN pp.zip
WHEN COALESCE(pi2.InsuredSameAsGuarantor , 1) = 1 THEN g.zip
ELSE ISNULL(pi2.zip , '')
END ,
'Insured2Country' = ISNULL(pi2.Country , '') ,
'Insured2Phone' = ISNULL(pi2.Phone1 , '') ,
'Insured2ID' = ISNULL(pi2.InsuredID , '') ,
'Insured2Group' = ISNULL(pi2.groupid , '') ,
'Ins2_EffDate' = CASE WHEN pi2.InsCardEffectiveDate IS NULL THEN ''
ELSE CONVERT(VARCHAR , pi2.InsCardEffectiveDate , 101)
END ,
'Insured2SSN' = CASE WHEN COALESCE(pi2.InsuredSameAsPatient , 1) = 1
THEN ISNULL(SUBSTRING(pp.SSN , 1 , 3) + '-' + SUBSTRING(pp.SSN , 4 , 2) + '-' + SUBSTRING(pp.SSN , 6 , 4) , '')
WHEN COALESCE(pi2.InsuredSameAsGuarantor , 1) = 1
THEN ISNULL(SUBSTRING(g.SSN , 1 , 3) + '-' + SUBSTRING(g.SSN , 4 , 2) + '-' + SUBSTRING(g.SSN , 6 , 4) , '')
ELSE ISNULL(SUBSTRING(pi2.SSN , 1 , 3) + '-' + SUBSTRING(pi2.SSN , 4 , 2) + '-' + SUBSTRING(pi2.SSN , 6 , 4) , '')
END ,
'Insured2DOB' = CASE WHEN COALESCE(pi2.InsuredSameAsPatient , 1) = 1 THEN CONVERT(VARCHAR(10) , pp.birthdate , 101)
WHEN COALESCE(pi2.InsuredSameAsGuarantor , 1) = 1 THEN CONVERT(VARCHAR(10) , g.birthdate , 101)
ELSE ISNULL(CONVERT(VARCHAR(10) , pi2.birthdate , 101) , '')
END,
'Operating Doctor' = ISNULL(od.Listname, ''),
'Other Physiscian' = ISNULL(oth.ListName, ''),
'Contact1First' = IsNull(pc.First,''),
'Contact1Middle' = IsNull(pc.Middle,''),
'Contact1Last' = IsNull(pc.Last,'')
FROM Appointments a
LEFT JOIN ApptChain ac ON a.ApptChainId = ac.ApptChainId
LEFT JOIN ApptSet aset ON a.ApptSetId = aset.ApptSetId
JOIN DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId
JOIN PatientProfile pp ON a.OwnerId = pp.PatientProfileId
JOIN Guarantor g ON pp.GuarantorID = g.GuarantorId
JOIN DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId
LEFT JOIN DoctorFacility df ON a.DoctorId = df.DoctorFacilityId
JOIN CasesInsurance ci ON ISNULL(a.CasesId , 0) = ISNULL(ci.CasesId , 0) AND ci.PatientProfileId = pp.PatientProfileId AND ci.OrderForClaims = 1
LEFT JOIN InsuranceCarriers ic ON ci.InsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN PatientInsurance pi ON ci.PatientInsuranceId = pi.PatientInsuranceId
LEFT JOIN Medlists ml ON ic.FinancialClassMid = ml.MedListsId
LEFT JOIN AllocationSet al ON ic.AllocationSetid = al.AllocationSetid
LEFT JOIN Medlists ml3 ON ic.CarrierTypeMid = ml3.MedListsId
LEFT JOIN Medlists ml4 ON ic.PolicyTypeMid = ml4.MedListsId
LEFT JOIN Medlists ml2 ON pi.PatRelToInsuredMID = ml2.MedListsId
LEFT JOIN Cases c ON ci.CasesId = c.CasesId AND ISNULL(c.WorkersComp , 0) <> 0
LEFT JOIN Employer e ON (c.EmployerId = e.EmployerId OR (c.EmployerId IS NULL AND pp.EmployerId = e.EmployerId))
LEFT JOIN Employer e2 ON (c.EmployerId = e2.EmployerId OR (c.EmployerId IS NULL AND g.EmployerId = e2.EmployerId))
LEFT JOIN Employer e3 ON (c.EmployerId = e3.EmployerId OR (c.EmployerId IS NULL AND pi.EmployerId = e3.EmployerId))
LEFT JOIN CasesInsurance ci2 ON ISNULL(a.CasesId , 0) = ISNULL(ci2.CasesId , 0) AND ci2.PatientProfileId = pp.PatientProfileId AND ci2.OrderForClaims = 2
LEFT JOIN InsuranceCarriers ic2 ON ci2.InsuranceCarriersId = ic2.InsuranceCarriersId
LEFT JOIN PatientInsurance pi2 ON ci2.PatientInsuranceId = pi2.PatientInsuranceId
LEFT JOIN Medlists ml5 ON pi2.PatRelToInsuredMID = ml5.MedListsId
LEFT JOIN DoctorFacility dfd ON a.DoctorId = dfd.DoctorFacilityId
LEFT JOIN ApptType at ON a.ApptTypeId = at.ApptTypeId
LEFT JOIN PatientVisit pv ON a.PatientVisitId = pv.PatientVisitId
LEFT JOIN DoctorFacility od ON pv.OperatingDoctorId = od.DoctorFacilityId
LEFT JOIN DoctorFacility oth ON pv.OtherDoctorId = oth.DoctorFacilityId
LEFT JOIN PatientContacts pc ON pp.PatientProfileId = pc.PatientProfileId
WHERE ApptKind = 1 AND ISNULL(Canceled,0) = 0 AND
a.ApptStart >= ISNULL('01/01/2008','1/1/1900') AND a.ApptStart < dateadd(d, 1, ISNULL('12/31/2008','1/1/3000'))
AND --Filter on doctor
(
(NULL IS NOT NULL AND a.ResourceID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND a.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER