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

Transact-SQL
1

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:

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, and I cannot even find a way to pull all of a patient's phone numbers on one single line. So these are two similar but not identical situations.

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... In this example, there is a table that gets me the patient entity ID, but that Entity ID has numerous entries in the "ENTY_phoneDetail" table (one for each phone number). This table has a Phone-ID and a PhoneType_ID, but not the phone numbers. They are in the ENTY_Phone table with the Phone_ID.
SO, I am pulling an entity-Id through several joins to get that far down. I can pull the phone used for scheduling the appointment because that phone ID is listed in an appointment table. However, pulling the patient with PhoneType_ID "1" which equals home, and PhoneType_ID "2" which equals Cell, etc. in separate columns on the same line has proven impossible for me.

Probably something simple for people who know what they are doing, right? And maybe it is just MORE joins???? It is also difficult to clearly demonstrate what I am dealing with because I am in a production healthcare database and cannot post any information I am actually pulling from this query.

TIA Gary

2

It's confusing because your CASE statement does match the description of your tables. That is, you state that the type and the actual phone are in different tables but the CASE statement shows you using the same table for both columns.

You only need a single JOIN to a subquery that pulls all the phone numbers together. It's easier to show in SQL code than to explain. If you could post the core parts of your SQL query, I can show you how to do this for multiple phone #s (more than 2 if needed).

3

Thanks for your quick response. To simplify things even more, I have a VIEW which has everything I need in it and I still cannot get the phones onto one line. I have not figured out that JOIN you are talking about to pul it together from the same table. Here is what I have for just the phone numbers. Once that is figured out, I am sure I can apply it to the bigger project.

USE TopsData

SELECT  VIEW_Phone.Entity_ID, 

		CASE VIEW_Phone.[PhoneType_Description] 
			WHEN 'Home' THEN VIEW_Phone.[Phone_Number]
			END AS 'Home Phone'

		,CASE VIEW_Phone.[PhoneType_Description]
			WHEN 'Cell' THEN VIEW_Phone.[Phone_Number]
			END AS 'Cell Phone'
		
FROM
  VIEW_Phone

Order by Entity_ID;

Which is giving me this:
|Entity_ID|Home Phone|Cell Phone|

|ZZZZZ00005|NULL|NULL|
|ZZZZZ00005|(123)456-7089|NULL|
|ZZZZZ00005|NULL|NULL|
|ZZZZZ00005|NULL|(570)867-5309|

4

The view is still hiding the actual JOIN of the tables. That part needs to be rewritten to best give you the output you want.

5

OK. Here is the entire thing for this particular section of information. I appreciate you taking a look and offering advice.
I need:
Entity, Phone number 1, Phone number 2, Phone number 3
a column for extensions might be nice too, but not required

Entity = Person
Phone type ID = Home, Cell, etc.
Phone ID matches up to the separate table with the phone numbers

example from table 1 (ENTY_PhoneDetail) with entity ZZZZZ0000H shows this person has 3 numbers in table 2:

PhoneDetail_ID Entity_ID Phone_ID PhoneDetail_RecordState PhoneType_ID PhoneDetail_System PhoneDetail_Note PhoneDetail_RowGuid PhoneDetail_CallSequence PhoneDetail_LastContentUpdate PhoneDetail_IsFavorite
ZZZZZ008GK ZZZZZ0000H ZZZZZ002WA 0 0000000001 1 E0DAE853-D428-457F-B345-8C393E39E599 0 NULL 1
ZZZZZ008GL ZZZZZ0000H ZZZZZ008CJ 0 0000000008 1 80E7BF90-3FC2-4C3F-986E-671C8743E7BD 0 NULL 0
ZZZZZ008GV ZZZZZ0000H ZZZZZ008CT 0 0000000004 1 3C541E0F-82C1-493B-8745-A142226CF305 0 NULL 0

table 2 (ENTY_Phone) - the phone numbers listed for this person:

Phone_ID Phone_Number Phone_Extension Phone_RecordState Phone_RowGuid Phone_LastContentUpdate
ZZZZZ002WA (912)990-1112 0 0221CA73-E299-4B6F-8DB3-7AB9FB9E1065 NULL
ZZZZZ008CJ (912)990-1113 0 D2B3EDEB-9632-455F-9A71-F3318155F7D0 NULL
ZZZZZ008CT (912)990-1114 5504 0 1D9DC7BF-4501-40F1-82D0-92ECED5F0481 NULL

There is a 3rd table, which associates the PhoneType_ID to the PhoneType_Description (I can do a CASE for these since there are only a few and they won't change if it is too difficult to make this 3rd association)

PhoneType_ID PhoneType_Description PhoneType_RecordState PhoneType_RowGuid PhoneType_LastContentUpdate
0000000000 0 CF9B1270-3FEE-4AF5-8AED-D37750E10B0E NULL
0000000008 Cell 0 CAE362EE-05F4-446C-AA76-997A22DEE418 NULL
0000000001 Home 0 3EFC2B9D-9F1D-4183-A069-2A6BD4CC7D79 NULL
0000000004 Office 0 CFF3EB66-41B1-4DDE-A57E-50DA27EF03F9 NULL

I have joined them, but I keep getting a separate row for each phone number, so in the example, there would be 3 rows with this entity:
SELECT
Entity_ID AS 'Person'

,CASE PHD.[PhoneType_ID]
WHEN '0000000001' THEN PHON.[Phone_Number]
END AS 'Home Phone'

,CASE PHD.[PhoneType_ID]
WHEN '0000000008' THEN PHON.[Phone_Number]
END AS 'Cell Phone'

,CASE PHD.[PhoneType_ID]
WHEN '0000000004' THEN PHON.[Phone_Number]
END AS 'Office Phone'

FROM
[TopsData].[dbo].[ENTY_PhoneDetail] AS PHD
JOIN [TopsData].[dbo].[ENTY_Phone] AS PHON ON PHD.Phone_ID = PHON.Phone_ID

WHERE Entity_ID = 'ZZZZZ0000H'

Gives me:

Person Home Phone Cell Phone Office Phone
ZZZZZ0000H (912)990-1112 NULL NULL
ZZZZZ0000H NULL (912)990-1113 NULL
ZZZZZ0000H NULL NULL (912)596-1114

I want

Person Home Phone Cell Phone Office Phone
ZZZZZ0000H (912)990-1112 (912)990-1113 (912)596-1114
6

Ah, that code does match your description, i.e. that the phone type and phone number are in two different tables.


SELECT
PHD.Entity_ID AS 'Person',
PHON.Home_Phone,
PHON.Cell_Phone,
PHON.Office_Phone
FROM 
[TopsData].[dbo].[ENTY_PhoneDetail] AS PHD
JOIN (
    SELECT Phone_ID,
        MAX(CASE WHEN [PhoneType_ID] = '0000000001' THEN [Phone_Number] END) AS Home_Phone,
        MAX(CASE WHEN [PhoneType_ID] = '0000000004' THEN [Phone_Number] END) AS Office_Phone,
        MAX(CASE WHEN [PhoneType_ID] = '0000000008' THEN [Phone_Number] END) AS Cell_Phone
    FROM [TopsData].[dbo].[ENTY_Phone]
    WHERE [PhoneType_ID] IN ('0000000001', '0000000004', '0000000008')
    GROUP BY [Phone_ID]
) AS PHON ON PHD.Phone_ID = PHON.Phone_ID
WHERE PHD.Entity_ID = 'ZZZZZ0000H'