[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2750
  • Last Modified:

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)
      )
0
Jeff S
Asked:
Jeff S
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

CASE WHEN Cast(PI.EligibilityNotes as varchar(8000)) like '%Declined IHC%'
     THEN 'No'
     WHEN Cast(PI.EligibilityNotes as varchar(8000)) like '%Decline IHC%'
     THEN 'No'
     WHEN Cast(PI.EligibilityNotes as varchar(8000)) like '%IHC%'
     THEN 'Yes'
     ELSE 'No'
     END AS [IHC Patient],
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now