Eliminate manual updating of case - when - then in my SQL queries

user_18348504 0
Viewed 19

I am NOT a SQL expert (obviously). I am learning things as I go because I have to. I have several different queries in which I am having to do a LOT of joins (which probably aren't done efficiently), and then, within that, I am having to manually create a lot of matches to information because I don't know how to get it into one query. It may not be possible, but is there a way to add into the query the ability to perform these additional links?

Example:

,CASE CHRTV.[HealthCare_ID]
    when 'ZZZZZ0006X' then 'Smith'
    when 'ZZZZZ00068' then 'Jones'
    when 'ZZZZZ0003D' then 'Adams'
    When 'ZZZZZ0003Y' then 'Gregory'
end AS 'Attending Dr. Last Name'

,CASE APP.[AppointmentType_ID]
    When '000000000F' THEN 'PE'
    When '0000000002' THEN 'PE15'
    When '0000000003' THEN 'PE30'
END AS 'Appointment Type

In this case, I have a join that gets me to the Healthcare_ID, but there is another "provider entity" table where the id matches the name and I cannot make that last. I am already starting from an appointment table, then joining that to chart visits and chart notes and providers and persons and entities... So, I am having to constantly update the list of providers' first names, last names, etc. in all of these scripts as people come and go.

I tried this (below) for one of the queries, but it gives me multiple lines, so not what I need because the table that has the phone numbers has as many entries for the patient entity_id as the patient has phone numbers in the system.

    ,CASE PHON.[PhoneType_Description]
        WHEN 'Home' THEN PHON.[Phone_Number] + ' ' + PHON.[PhoneType_Description]
        WHEN 'Cell' THEN PHON.[Phone_Number] + ' ' + PHON.[PhoneType_Description]
    END AS 'Phone'

So I get:

Facility ID Resource    Appointment start time  Appointment Description Patient First Name  Patient Last Name   Phone
Clinic 1    Jones   3/1/2022 9:00   3 month follow up   Jenny   Smith   (123)456-7890 Cell
Clinic 1    Jones   3/1/2022 9:00   3 month follow up   Jenny   Smith   (123)867-5309 Home

Which has all the info I need, but I need the 2 phone numbers on one line... Obviously, these examples are sort of scattered, but they all show the one type of result I am attempting to get.

Probably something simple for people who know what they are doing, right? And maybe it is just MORE joins????

TIA Gary

sql
1 Answers

I give you an solution for your first example. Once you have understood the prinicple you can repeat for the other examples.
You need to store the correspondance between the codes and the names in a table, and use another join. The advantage is that you can easily add or remove id-name pairs without rewriting your case statement in the query.
You can also use a foreign key constraint to ensure that all entries in CHRTV have a corresponding entry in the correspondance table.
Usually this table would be called user, customers etc. according to the use and contain other information such as telephone number, adress. In your case it might be called "Doctors". You may already have such a table and just need to add a column.

create table HealthCare_IDs(
id varchar(10)  primary key,
name varchar(25) not null);
insert into HealthCare_IDs values
('ZZZZZ0006X', 'Smith'),
('ZZZZZ00068', 'Jones'),
('ZZZZZ0003D', 'Adams'),
('ZZZZZ0003Y', 'Gregory');

CREATE TABLE CHRTV (
id int primary key auto_increment,
HealthCare_ID varchar(10) not null);
insert into CHRTV (HealthCare_ID) values
('ZZZZZ0006X'), 
('ZZZZZ00068'), 
('ZZZZZ0003D'), 
('ZZZZZ0003Y');

SELECT c.id, h.name
FROM CHRTV c
JOIN HealthCare_IDs h
ON c.HealthCare_ID = h.id;

id | name   
-: | :------
 3 | Adams  
 4 | Gregory
 2 | Jones  
 1 | Smith

db<>fiddle here

Admin1
Related