Link to home
Start Free TrialLog in
Avatar of Jeff S
Jeff SFlag for United States of America

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(PatientSameAsGuarantor,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.InsCardEffectiveDate,101),'')AS [PrimaryInsEffDate],
       ISNULL(Convert(VarChar(20),pi.inscardterminationdate,101),'')AS [PrimaryInsTermDate],
      ISNULL(pp.MedicalRecordNumber,'') AS [Medical Record #],
      ISNULL(Convert(VarChar(20),pp.birthdate,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.Address1, PatientRespAddr2=pr.Address2, PatientRespCity=pr.City,
      PatientRespState=pr.State, PatientRespZip=pr.Zip, FinancialClass=isnull(ml.Description,'none'),
      Doctor=df.ListName,Facility=df1.OrgName,Balance=isnull(ppa.PatBalance,0)+isnull(ppa.InsBalance,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)
      )
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial