Need to append 2 columns
-
Hi. Sorry for my original post not being clear. I think I am providing everything needed and I am clear now.
This code uses the table ClientVisit and I'm including the create table for ClientVisit
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;ClientVisit 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)And now I would like to append 2 additional columns by adding code to the code above. Columns: Primary Program and PP Length of Stay .
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) -
Note: From the data you provided program_id from client_vist cannot be joined with client program because they do not match.
May need some code tweaks... to get the correct program_id code, commented the join ... on program_id.
I just pulled the columns required using CROSS APPLY to join to your main query.
;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
,Progcode.program_id 'Primary program'
,ClientEp.program_id
,ClientEp.length_of_stay 'PP LengthOfStay'
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
CROSS APPLY(select top 1 program_id
from ClientPrograms cp
where t.client_id = cp.client_id
order by cp.program_id desc
--and t.program_id = cp.program_id
)ProgCode
CROSS APPLY(select top 1 program_id, length_of_stay
from ClientEpisode ce
where t.client_id = ce.client_id
order by ce.program_id desc
--and t.program_id = cp.program_id
)clientEp
WHERE t.timein = l.latest_visit_date
OR lpv.latest_primary_visit_date IS NOT NULL
ORDER BY t.client_id, t.timein DESC;=======================================================================
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply