Jeff S
asked on
T-SQL Help Needed - Case Statement Help
I need assistance with the following line of code:
CASE WHEN Cast(PI.EligibilityNotes as varchar(8000)) like '%IHC%' THEN 'Yes' ELSE 'No' END AS [IHC Patient],
Previously, this was ok and working .... then the client came back and added a small twist to it. They mentioned they used "Declined IHC" or "Decline IHC" (ommitting the d) --- thus making this a 'No". So they are using IHC by itself as a 'YES' and a "Declined IHC' or a 'Decline IHC' for a 'NO'.
Can someone lend me a hand here?
My query:
SET NOCOUNT ON
---DECLARE @Zip varchar(40)
---SELECT @Zip = LTRIM(RTRIM('NULL')) + '%'
SELECT PatientID,
RespSameAsPatient=isnull(P atientSame AsGuaranto r,0),
CASE WHEN Cast(PI.EligibilityNotes as varchar(8000)) like '%IHC%' THEN 'Yes' ELSE 'No' END AS [IHC Patient],
pi.eligibilitynotes,
PatientName=CASE
WHEN RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) <> '' THEN
RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) + ', ' + ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,''))
ELSE RTRIM(ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,''))
END,
CASE
WHEN pp.Sex = 'M' THEN 'Male'
WHEN pp.Sex = 'F' THEN 'Female'
ELSE ISNULL(pp.Sex,'')
END AS [Patient Sex],
IsNull(ic.Name,'')AS 'Carrier Name',
pi.InsuredID,
pi.GroupID,
ISNULL(Convert(VarChar(20) ,pi.InsCar dEffective Date,101), '')AS [PrimaryInsEffDate],
ISNULL(Convert(VarChar(20) ,pi.inscar dterminati ondate,101 ),'')AS [PrimaryInsTermDate],
ISNULL(pp.MedicalRecordNum ber,'') AS [Medical Record #],
ISNULL(Convert(VarChar(20) ,pp.birthd ate,101),' ') as [Patient DOB],
(select top 1 apptstart from appointments a where a.ownerID = pp.PatientProfileID and datediff(day, getDate(), apptstart) < 0 order by apptstart desc) as [Last Appt Date],
(select top 1 apptstart from appointments a where a.ownerID = pp.PatientProfileID and datediff(day, getDate(), apptstart) > 0 order by apptstart asc) as [Next Appt Date],
ISNULL(pcp.Listname,'') AS PCP,
PatientAddr1=pp.Address1, PatientAddr2=pp.Address2,
PatientCity=pp.City, PatientState=pp.State, PatientZip=pp.Zip,
ISNULL(dbo.formatphone(pp. phone1,1), '') AS [Phone 1 #],
ISNULL(pp.Phone1Type,'') AS [Phone 1 Type],
ISNULL(dbo.formatphone(pp. Phone2,1), '') AS [Phone 2 #],
ISNULL(pp.Phone2Type,'') AS [Phone 2 Type],
PatientRespName=CASE
WHEN RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) <> '' THEN
RTRIM(RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) + ', ' + ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,''))
ELSE RTRIM(ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,''))
END,
PatientRespAddr1=pr.Addres s1, PatientRespAddr2=pr.Addres s2, PatientRespCity=pr.City,
PatientRespState=pr.State, PatientRespZip=pr.Zip, FinancialClass=isnull(ml.D escription ,'none'),
Doctor=df.ListName,Facilit y=df1.OrgN ame,Balanc e=isnull(p pa.PatBala nce,0)+isn ull(ppa.In sBalance,0 )
---pp.DeathDate,
---CASE WHEN ISNULL(pp.Inactive,0) = 0 THEN 'No' ELSE 'Yes' END AS Inactive
FROM PatientProfile pp
JOIN PatientProfileAgg ppa ON pp.PatientProfileID = ppa.PatientProfileID
LEFT JOIN Guarantor pr ON pp.GuarantorID = pr.GuarantorID
LEFT JOIN MedLists ml ON pp.FinancialClassMID = ml.MedListsID
LEFT JOIN DoctorFacility df ON pp.DoctorID = df.DoctorFacilityID
LEFT JOIN DoctorFacility df1 ON pp.FacilityId = df1.DoctorFacilityID
LEFT JOIN DoctorFacility pcp ON pp.PrimaryCareDoctorId = pcp.DoctorFacilityId
LEFT JOIN PatientInsurance pi ON pp.PatientProfileID = pi.PatientProfileID AND pi.orderforclaims = 1
LEFT JOIN InsuranceCarriers ic ON pi.InsuranceCarriersID = ic.InsuranceCarriersId
WHERE --Filter on patient
(
(NULL IS NOT NULL AND pp.PatientProfileID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on doctor
(
(NULL IS NOT NULL AND pp.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on doctor
(
(NULL IS NOT NULL AND pp.PrimaryCareDoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on insurance carrier
(
(NULL IS NOT NULL AND ic.InsuranceCarriersId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND pp.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
CASE WHEN Cast(PI.EligibilityNotes as varchar(8000)) like '%IHC%' THEN 'Yes' ELSE 'No' END AS [IHC Patient],
Previously, this was ok and working .... then the client came back and added a small twist to it. They mentioned they used "Declined IHC" or "Decline IHC" (ommitting the d) --- thus making this a 'No". So they are using IHC by itself as a 'YES' and a "Declined IHC' or a 'Decline IHC' for a 'NO'.
Can someone lend me a hand here?
My query:
SET NOCOUNT ON
---DECLARE @Zip varchar(40)
---SELECT @Zip = LTRIM(RTRIM('NULL')) + '%'
SELECT PatientID,
RespSameAsPatient=isnull(P
CASE WHEN Cast(PI.EligibilityNotes as varchar(8000)) like '%IHC%' THEN 'Yes' ELSE 'No' END AS [IHC Patient],
pi.eligibilitynotes,
PatientName=CASE
WHEN RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) <> '' THEN
RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) + ', ' + ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,''))
ELSE RTRIM(ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,''))
END,
CASE
WHEN pp.Sex = 'M' THEN 'Male'
WHEN pp.Sex = 'F' THEN 'Female'
ELSE ISNULL(pp.Sex,'')
END AS [Patient Sex],
IsNull(ic.Name,'')AS 'Carrier Name',
pi.InsuredID,
pi.GroupID,
ISNULL(Convert(VarChar(20)
ISNULL(Convert(VarChar(20)
ISNULL(pp.MedicalRecordNum
ISNULL(Convert(VarChar(20)
(select top 1 apptstart from appointments a where a.ownerID = pp.PatientProfileID and datediff(day, getDate(), apptstart) < 0 order by apptstart desc) as [Last Appt Date],
(select top 1 apptstart from appointments a where a.ownerID = pp.PatientProfileID and datediff(day, getDate(), apptstart) > 0 order by apptstart asc) as [Next Appt Date],
ISNULL(pcp.Listname,'') AS PCP,
PatientAddr1=pp.Address1, PatientAddr2=pp.Address2,
PatientCity=pp.City, PatientState=pp.State, PatientZip=pp.Zip,
ISNULL(dbo.formatphone(pp.
ISNULL(pp.Phone1Type,'') AS [Phone 1 Type],
ISNULL(dbo.formatphone(pp.
ISNULL(pp.Phone2Type,'') AS [Phone 2 Type],
PatientRespName=CASE
WHEN RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) <> '' THEN
RTRIM(RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) + ', ' + ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,''))
ELSE RTRIM(ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,''))
END,
PatientRespAddr1=pr.Addres
PatientRespState=pr.State,
Doctor=df.ListName,Facilit
---pp.DeathDate,
---CASE WHEN ISNULL(pp.Inactive,0) = 0 THEN 'No' ELSE 'Yes' END AS Inactive
FROM PatientProfile pp
JOIN PatientProfileAgg ppa ON pp.PatientProfileID = ppa.PatientProfileID
LEFT JOIN Guarantor pr ON pp.GuarantorID = pr.GuarantorID
LEFT JOIN MedLists ml ON pp.FinancialClassMID = ml.MedListsID
LEFT JOIN DoctorFacility df ON pp.DoctorID = df.DoctorFacilityID
LEFT JOIN DoctorFacility df1 ON pp.FacilityId = df1.DoctorFacilityID
LEFT JOIN DoctorFacility pcp ON pp.PrimaryCareDoctorId = pcp.DoctorFacilityId
LEFT JOIN PatientInsurance pi ON pp.PatientProfileID = pi.PatientProfileID AND pi.orderforclaims = 1
LEFT JOIN InsuranceCarriers ic ON pi.InsuranceCarriersID = ic.InsuranceCarriersId
WHERE --Filter on patient
(
(NULL IS NOT NULL AND pp.PatientProfileID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on doctor
(
(NULL IS NOT NULL AND pp.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on doctor
(
(NULL IS NOT NULL AND pp.PrimaryCareDoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on insurance carrier
(
(NULL IS NOT NULL AND ic.InsuranceCarriersId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND pp.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.