CODEWITHSUNDEEP
sqlsql-serverjoin
Felli Buds
Felli Buds

Reputation: 31

Need to have LIKE and specific code in one sql

In the following, I am getting patients who have visited Department 2 and also have a diagnosis_code in starting with 'E11.' .

I also would like another group of patients who have a specific code 'I10'.

How can I code here for both groups. I would actually like to create a column for each. For E11. 'DIAB'. For I10 'HYPER', in addition to the other columns we are showing. The patient can have had both of these btw.

SELECT 
    Vouchers.Billing_Date, 
    Vouchers.Voucher_ID, 
    vwGenPatApptInfo.Appt_Status, 
    vwGenPatApptInfo.Appt_Sched_Department_ID, 
    vwGenPatInfo.Patient_First_Name, 
    vwGenPatInfo.Patient_Last_Name, 
    Vouchers.Patient_ID, 
    Diagnosis_Codes.Diagnosis_Code
 FROM   
    ((Ntier_70751.PM.Service_Diagnoses Service_Diagnoses 
        INNER JOIN Ntier_70751.PM.Services Services ON Service_Diagnoses.Service_ID=Services.Service_ID) 
    INNER JOIN Ntier_70751.PM.Diagnosis_Codes Diagnosis_Codes ON Service_Diagnoses.Diagnosis_Code_ID=Diagnosis_Codes.Diagnosis_Code_ID)    
   INNER JOIN ((Ntier_70751.PM.vwGenPatApptInfo vwGenPatApptInfo 
        INNER JOIN Ntier_70751.PM.Vouchers Vouchers ON vwGenPatApptInfo.Patient_ID=Vouchers.Patient_ID) 
        INNER JOIN Ntier_70751.PM.vwGenPatInfo vwGenPatInfo ON vwGenPatApptInfo.Patient_ID=vwGenPatInfo.Patient_ID) 
        ON Services.Voucher_ID=Vouchers.Voucher_ID
 WHERE  
    vwGenPatApptInfo.Appt_Sched_Department_ID=2 
    AND vwGenPatApptInfo.Appt_Status='A' 
    AND Vouchers.Billing_Date>=N'01-01-2016' 
    AND Diagnosis_Codes.Diagnosis_Code LIKE 'E11.%'
 ORDER BY Vouchers.Patient_ID

Upvotes: 1

Views: 70

Answers (5)

Serg
Serg

Reputation: 22811

If you need both DIAB and HYPER in a single row you can pivot them this way, using conditional aggregation 

SELECT 
    Vouchers.Billing_Date, 
    Vouchers.Voucher_ID, 
    vwGenPatApptInfo.Appt_Status, 
    vwGenPatApptInfo.Appt_Sched_Department_ID, 
    vwGenPatInfo.Patient_First_Name, 
    vwGenPatInfo.Patient_Last_Name, 
    Vouchers.Patient_ID, 
    Diagnosis_Codes.Diagnosis_Code,
    max(case when Diagnosis_Codes.Diagnosis_Code like 'E11.%' then Diagnosis_Codes.Diagnosis_Code end) as DIAB,
    max(case when Diagnosis_Codes.Diagnosis_Code = 'I10' then Diagnosis_Codes.Diagnosis_Code end) as HYPER
FROM   
      ((Ntier_70751.PM.Service_Diagnoses Service_Diagnoses 
        INNER JOIN Ntier_70751.PM.Services Services ON Service_Diagnoses.Service_ID=Services.Service_ID) 
      INNER JOIN Ntier_70751.PM.Diagnosis_Codes Diagnosis_Codes ON Service_Diagnoses.Diagnosis_Code_ID=Diagnosis_Codes.Diagnosis_Code_ID)    
    INNER JOIN 
      ((Ntier_70751.PM.vwGenPatApptInfo vwGenPatApptInfo 
        INNER JOIN Ntier_70751.PM.Vouchers Vouchers ON vwGenPatApptInfo.Patient_ID=Vouchers.Patient_ID) 
      INNER JOIN Ntier_70751.PM.vwGenPatInfo vwGenPatInfo ON vwGenPatApptInfo.Patient_ID=vwGenPatInfo.Patient_ID) 
    ON Services.Voucher_ID=Vouchers.Voucher_ID
 WHERE  
    vwGenPatApptInfo.Appt_Sched_Department_ID=2 
    AND vwGenPatApptInfo.Appt_Status='A' 
    AND Vouchers.Billing_Date>=N'01-01-2016' 
    AND (Diagnosis_Codes.Diagnosis_Code = 'I10' or Diagnosis_Codes.Diagnosis_Code LIKE 'E11.%')
GROUP BY
    Vouchers.Billing_Date, 
    Vouchers.Voucher_ID, 
    vwGenPatApptInfo.Appt_Status, 
    vwGenPatApptInfo.Appt_Sched_Department_ID, 
    vwGenPatInfo.Patient_First_Name, 
    vwGenPatInfo.Patient_Last_Name, 
    Vouchers.Patient_ID, 
    Diagnosis_Codes.Diagnosis_Code
ORDER BY Vouchers.Patient_ID

Upvotes: 0

Senthil_Arun
Senthil_Arun

Reputation: 1078

use UNION operator

    SELECT <BLOCK> Code='E11.%' 
    UNION
    SELECT <BLOCK> Code='I10%'

Upvotes: 0

Cyndi Baker
Cyndi Baker

Reputation: 679

SELECT 
    Vouchers.Billing_Date, 
    Vouchers.Voucher_ID, 
    vwGenPatApptInfo.Appt_Status, 
    vwGenPatApptInfo.Appt_Sched_Department_ID, 
    vwGenPatInfo.Patient_First_Name, 
    vwGenPatInfo.Patient_Last_Name, 
    Vouchers.Patient_ID, 
    Diagnosis_Codes.Diagnosis_Code,
    case when Diagnosis_Codes.Diagnosis_Code like 'E11.%' then 1 else 0 end as DIAB,
    case when Diagnosis_Codes.Diagnosis_Code like 'E10.%' then 1 else 0 end as HYPER
    FROM   
    ((Ntier_70751.PM.Service_Diagnoses Service_Diagnoses 
        INNER JOIN Ntier_70751.PM.Services Services ON Service_Diagnoses.Service_ID=Services.Service_ID) 
    INNER JOIN Ntier_70751.PM.Diagnosis_Codes Diagnosis_Codes ON Service_Diagnoses.Diagnosis_Code_ID=Diagnosis_Codes.Diagnosis_Code_ID)    
    INNER JOIN ((Ntier_70751.PM.vwGenPatApptInfo vwGenPatApptInfo 
        INNER JOIN Ntier_70751.PM.Vouchers Vouchers ON vwGenPatApptInfo.Patient_ID=Vouchers.Patient_ID) 
        INNER JOIN Ntier_70751.PM.vwGenPatInfo vwGenPatInfo ON vwGenPatApptInfo.Patient_ID=vwGenPatInfo.Patient_ID) 
        ON Services.Voucher_ID=Vouchers.Voucher_ID
    WHERE  
    vwGenPatApptInfo.Appt_Sched_Department_ID=2 
    AND vwGenPatApptInfo.Appt_Status='A' 
    AND Vouchers.Billing_Date>=N'01-01-2016' 
    AND (Diagnosis_Codes.Diagnosis_Code LIKE 'E11.%' or Diagnosis_Codes.Diagnosis_Code LIKE 'E10.%')
    ORDER BY Vouchers.Patient_ID

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270583

I think this should do what you want:

WHERE . . .
      AND (Diagnosis_Codes.Diagnosis_Code LIKE 'E11.%' OR 
           Diagnosis_Codes.Diagnosis_Code = 'I10'
          )

Note: I didn't rewrite the whole query, but you should learn to use table aliases. They make the query easier to write and to read.

Upvotes: 1

Moptan
Moptan

Reputation: 336

Try replacing 

"Diagnosis_Codes"."Diagnosis_Code" LIKE 'E11.%'

with

("Diagnosis_Codes"."Diagnosis_Code" LIKE 'E11.%' or "Diagnosis_Codes"."Diagnosis_Code" LIKE 'I10.%')

and see if that gives the result you want :)

Upvotes: 2

Related Questions