troubleshooting Question

SQL - Sub Query (Select Top 1) Help Needed

Avatar of Jeff S
Jeff SFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
3 Comments1 Solution399 ViewsLast Modified:
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.
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)
	)
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros