Need to add columns to table from lookup to 2 other tables

Question

Need to add columns to table from lookup to 2 other tables

Old Hand

Points: 340

March 6, 2023 at 12:00 am

Hi there. Thanks for code from my previous post and so many good working options. Now I want to add 2 new columns. Appreciate any help.

From this table, I have the code that selects the latest doctor visit record using the MAX function. If latest client record is not with the primary doc but a staff doctor, then show that staff doctor record and then also show the latest record the patient had with the primary doctor. If the latest record (by date) is with the primary doctor, then just show that record. This is working great.

creates main table

CREATE TABLE ClientVisit (
    clientvisit_id int,
    client_id int,
    emp_id int,
    primarydoc int,
    program_id int,
    visittype_id int,
    visittype varchar(50),
    timein date,
    clientfname varchar(50),
    clientlname varchar(50),
    emp_name varchar(50),
    episode_id int
);

INSERT INTO ClientVisit (clientvisit_id, client_id, emp_id, primarydoc, program_id, visittype_id, visittype, timein, clientfname, clientlname, emp_name, episode_id)
VALUES
(226984,1003130,6626,6197,21,160,'yCancel No Show','2022-12-14 16:00:00.0000000','Samson','Willrett','Annette Bening',53315),
(243781,1003130,6197,6197,21,19,'Ind Aromatherapy','2022-12-13 16:00:00.0000000','Samson','Willrett','James Bond',53315),
(247364,1003130,6197,6197,21,194,'Treatment Plan','2022-12-13 14:23:00.0000000','Samson','Willrett','James Bond',53315),
(233227,1003130,6197,6197,21,160,'yCancel No Show','2022-12-06 15:00:00.0000000','Samson','Willrett','James Bond',53315),
(228263,1003130,6197,6197,21,19,'Ind Aromatherapy','2022-12-01 13:00:00.0000000','Samson','Willrett','James Bond',53315),
(229109,1003681,6626,6197,21,160,'yCancel No Show','2022-12-29 09:00:00.0000000','Cora','Bears','Annette Bening',53860),
(245877,1003681,6197,6197,21,19,'*Ind Aromatherapy','2022-12-15 09:08:00.0000000','Cora','Bears','James Bond',53860),
(226998,1003681,6197,6197,21,19,'Ind Aromatherapy','2022-12-01 09:00:00.0000000','Cora','Bears','James Bond',53860),
(234595,1010341,6626,6197,33,160,'yCancel No Show','2023-01-31 13:00:00.0000000','Barbara','Bush','Annette Bening',56018),
(253286,1010341,6197,6197,21,160,'yCancel No Show','2022-12-21 10:00:00.0000000','Barbara','Bush','James Bond',55555),
(266461,1010907,6197,6197,21,19,'*Ind Aromatherapy','2022-12-29 16:02:00.0000000','Kirk','Outbound','James Bond',59704),
(267636,1004114,6197,6197,33,19,'*Ind Aromatherapy','2022-12-29 14:00:00.0000000','Jarrett','Yelling','James Bond',54188)

from this table I run this code:

go

WITH latest_visits AS (
    SELECT 
        client_id, 
        MAX(timein) AS latest_visit_date
    FROM ClientVisit
    GROUP BY client_id
), 
latest_primary_visits AS (
    SELECT 
        t.client_id,
        t.emp_id,
        MAX(t.timein) AS latest_primary_visit_date
    FROM ClientVisit t
    WHERE t.emp_id = t.primarydoc
    GROUP BY t.client_id, t.emp_id
)
SELECT 
    t.*, 
    CASE 
        WHEN t.timein = l.latest_visit_date AND t.emp_id <> t.primarydoc THEN 'Last visit seen by staff doctor' 
        WHEN t.timein = l.latest_visit_date AND t.emp_id = t.primarydoc THEN 'Last visit seen by primary doctor'
        WHEN t.timein <> l.latest_visit_date AND t.emp_id = t.primarydoc THEN 'Last visit seen by primary doctor (previous visit)' 
    END AS seen_by_primary
FROM ClientVisit t
JOIN latest_visits l ON t.client_id = l.client_id
LEFT JOIN latest_primary_visits lpv ON t.client_id = lpv.client_id
    AND t.emp_id = lpv.emp_id
    AND t.timein = lpv.latest_primary_visit_date
WHERE t.timein = l.latest_visit_date
    OR lpv.latest_primary_visit_date IS NOT NULL
ORDER BY t.client_id, t.timein DESC;

Works great. And now I would like to append 2 additional columns by adding code to the code above. Primary Program and PP Length of Stay . Please let me know if you need any additional info. thanks

table4post

To find the Primary Program and PP Length of Stay requires lookup of 2 tables: ClientEpisode table and ClientPrograms table. Client Programs table lists each client and their primary program. e.g. 1003130 is 44. Knowing that 1003130 has primary program 44 because primary_flag is True, when looking up in the Primary Program table, we can then do a lookup in the ClientEpisode table and find the episode record for that client with a program_id of 44 and then get the length_of_stay from that episode record.

To create the 2 tables I have provided the code:

CREATE TABLE ClientPrograms (
    client_id int,
    program_id int,
    primary_flag varchar(50)
);

INSERT INTO ClientPrograms (client_id, program_id, primary_flag)
VALUES
(1003130, 44, 'True'),
(1003130, 21, 'False'),
(1003681, 45, 'True'),
(1003681, 31, 'False'),
(1010907, 45, 'True'),
(1010907, 33, 'False'),
(1010341, 47, 'True'),
(1010341, 81, 'False'),
(1004114, 48, 'True'),
(1004114, 21, 'False')

CREATE TABLE ClientEpisode (
    episode_id int,
    episode_status varchar(50),
    client_id int,
    admission_date date,
    discharge_date date,
    program_id int,
    length_of_stay int
);
INSERT INTO ClientEpisode (episode_id, episode_status, client_id, admission_date, discharge_date, program_id, length_of_stay)
VALUES

(53860,'Active',1003681,'2022-05-18','2023-01-21',21,54),
(59704,'Active',1010907,'2022-05-18','2023-01-21',21,25),
(54188,'Active',1004114,'2022-05-18','2023-01-21',33,88),
(56018,'Active',1010341,'2022-05-18','2023-01-21',33,11),
(53315,'Active',1003130,'2022-05-18','2023-01-21',21,274),
(43860,'Active',1003681,'2022-05-18','2023-01-21',45,333),
(49704,'Active',1010907,'2022-05-18','2023-01-21',45,444),
(44188,'Active',1004114,'2022-05-18','2023-01-21',48,555),
(46018,'Active',1010341,'2022-05-18','2023-01-21',47,666),
(55555,'Active',1010341,'2022-05-18','2023-01-21',37,123),
(43315,'Active',1003130,'2022-05-18','2023-01-21',44,777)

This topic was modified 1 year, 8 months ago by jpgdrive.
This topic was modified 1 year, 8 months ago by jpgdrive. Reason: modified to make more clear

Attachments:

You must be logged in to view attached files.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Emperor100 SSCrazy Points: 2173 More actions · Answer 1

I tried and found only ClientPrograms table is joined but not ClientEpisodes. From the data provided for ClientPrograms and ClientEpisodes. It looks like one-many relationship from ClientEpisodes to ClientPrograms.

Have you pasted the final query with new code embedded?

=======================================================================

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

What errors does it give?

Do we have to find your previous post to work out what to do in this question?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

You've posted delimited data. Take it to the next step and post it as readily consumable data and you might get better answers because more people will give it a try. See the first link in my signature line below for one of many ways to create readily consumable data. Also, it would be helpful if you put your code into a code window using the code icon ...

...in the menu at the top of the edit window when you're creating your post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jpgdrive Old Hand Points: 340 More actions · Answer 4

I'm working on fixing the explanation with the create table code and inserts. thanks