December 3, 2020 at 4:44 pm
How do I exclude the "Appointments" that are not adjacent to a "Discharge"? This is a patient with doctor visits and hospital discharges. If you order by the VisitDate, you will see a chronological record for the patient. I need to identify the last doctor visit before discharge then, the next appointment after discharge for each discharge from the hospital. There are some discharges without a doctors visit between. Put another way, exclude the Appointments not associated with a discharge. Any thoughts?
create table #T
(
MTMRN varchar(30),
VisitDate datetime,
VisitType varchar(30)
)
insert into #T(MTMRN, VisitDate, VisitType) values('x51xx31','2020-02-18','Appointment')
insert into #T(MTMRN, VisitDate, VisitType) values('x51xx31','2020-03-10','Appointment')
insert into #T(MTMRN, VisitDate, VisitType) values('x51xx31','2020-03-24','Appointment')
insert into #T(MTMRN, VisitDate, VisitType) values('x51xx31','2020-03-31','Discharge')
insert into #T(MTMRN, VisitDate, VisitType) values('x51xx31','2020-04-16','Discharge')
insert into #T(MTMRN, VisitDate, VisitType) values('x51xx31','2020-04-19','Discharge')
insert into #T(MTMRN, VisitDate, VisitType) values('x51xx31','2020-04-21','Appointment')
insert into #T(MTMRN, VisitDate, VisitType) values('x51xx31','2020-04-24','Appointment')
insert into #T(MTMRN, VisitDate, VisitType) values('x51xx31','2020-05-19','Appointment')
insert into #T(MTMRN, VisitDate, VisitType) values('x51xx31','2020-06-15','Appointment')
insert into #T(MTMRN, VisitDate, VisitType) values('x51xx31','2020-07-07','Appointment')
insert into #T(MTMRN, VisitDate, VisitType) values('x51xx31','2020-07-08','Discharge')
insert into #T(MTMRN, VisitDate, VisitType) values('x51xx31','2020-08-17','Appointment')
insert into #T(MTMRN, VisitDate, VisitType) values('x51xx31','2020-09-02','Appointment')
insert into #T(MTMRN, VisitDate, VisitType) values('x51xx31','2020-09-29','Appointment')
insert into #T(MTMRN, VisitDate, VisitType) values('x51xx31','2020-09-30','Appointment')
December 3, 2020 at 5:53 pm
I may be getting this wrong... This only finds the last appointment before a discharge and all discharges. Please mark your sample data for what is expected in the return if the output from the following is not correct.
WITH cte AS
(
SELECT *
,NextVisitType = LEAD(VisitType,1,NULL) OVER (PARTITION BY MTMRN ORDER BY MTMRN, VisitDate)
FROM #T
)
SELECT MTMRN,VisitDate,VisitType
FROM cte
WHERE (VisitType = 'Appointment' AND NextVisitType = 'Discharge')
OR VisitType = 'Discharge'
ORDER BY MTMRN,VisitDate
;
--Jeff Moden
Change is inevitable... Change for the better is not.
December 3, 2020 at 6:18 pm
I assumed by "for each Discharge", you wanted to see every discharge, with only their before and after appointments. If that's not what you wanted, please clarify your requirements.
SELECT
T_Discharge.MTMRN, T_Discharge.VisitDate AS Discharge_Date,
T_before.VisitDate AS Appt_Date_Before_Discharge,
T_after.VisitDate AS Appt_Date_After_Discharge
FROM #T T_Discharge
OUTER APPLY (
SELECT TOP (1) *
FROM #T T_before
WHERE T_before.MTMRN = T_Discharge.MTMRN AND
T_before.VisitDate < T_Discharge.VisitDate AND
T_before.VisitType = 'Appointment'
ORDER BY T_before.VisitDate DESC
) AS T_before
OUTER APPLY (
SELECT TOP (1) *
FROM #T T_after
WHERE T_after.MTMRN = T_Discharge.MTMRN AND
T_after.VisitDate > T_Discharge.VisitDate AND
T_after.VisitType = 'Appointment'
ORDER BY T_after.VisitDate
) AS T_after
WHERE T_Discharge.VisitType = 'Discharge'
ORDER BY MTMRN, Discharge_Date
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 3, 2020 at 6:28 pm
Based on my previous misgivings about the problem definition, here's one that returns "Appointments" that are returned if they immediately precede or are followed by a "Discharge". It returns all "Discharge" rows, regardless.
If neither of these solutions do what you want, then you really do need to mark the rows in the sample data you provided as to those that you actually want returned. You should also get into the habit of doing such on future posts, please.
WITH cte AS
(
SELECT *
,PrevVisitType = LAG (VisitType,1,NULL) OVER (PARTITION BY MTMRN ORDER BY MTMRN, VisitDate)
,NextVisitType = LEAD(VisitType,1,NULL) OVER (PARTITION BY MTMRN ORDER BY MTMRN, VisitDate)
FROM #T
)
SELECT MTMRN,VisitDate,VisitType,PrevVisitType,NextVisitType
FROM cte
WHERE (VisitType = 'Appointment' AND PrevVisitType = 'Discharge')
OR (VisitType = 'Appointment' AND NextVisitType = 'Discharge')
OR (VisitType = 'Discharge')
ORDER BY MTMRN,VisitDate
;
--Jeff Moden
Change is inevitable... Change for the better is not.
December 14, 2020 at 4:42 pm
>> How do I exclude the "Appointments" that are not adjacent to a "Discharge"? <<
Your problem is the true data model is wrong. An appointment is made up of two parts because of a temporal interval. It has a beginning and it has an end. The ending of such an event can be null if the event is still ongoing. Download a free copy of temporal queries in SQL by Richard Snodgrass. It's available as a PDF from the University of Arizona computer science department website.
The reason you made this common mistake is that you still think of a clipboard and a physical sign in/sign out sheet. You didn't even know that a table has to have a key so you declared it in such a way that it would be impossible for your DDL to ever have a key. You also use the term "adjacent" which is a spatial concept that doesn't apply to the table. A table is an abstract unordered set of rows. If this is a list of patient appointments, where is the patient identifier? Is that what "mtmrn" means? Why do you think that T# is a clear, precise and useful name for a table?
CREATE TABLE Appointments
(mtmrn VARCHAR (30) NOT NULL, --- no idea what this means
visit_date DATE NOT NULL,
PRIMARY KEY (mtmrn, visit_date), --- keys are not an option!
discharge_date DATE); -- NULLs mean not finished yet
Microsoft has had the ANSI/ISO standard row constructors for quite a few years now. There is no need to use the old Sybase punchcard style one at a time Insertions. Here is one possible way of arranging your data.
INSERT INTO Appointments
VALUES
('x51xx31', '2020-03-24', '2020-04-19'),
('x51xx31', '2020-02-18', '2020-03-31'),
('x51xx31', '2020-03-10', '2020-04-16'),
('x51xx31', '2020-04-21', NULL),
('x51xx31', '2020-04-24', NULL),
('x51xx31', '2020-05-19', NULL),
('x51xx31', '2020-06-15', NULL),
('x51xx31', '2020-07-07', '2020-07-08'),
('x51xx31', '2020-08-17', NULL),
('x51xx31', '2020-09-02', NULL),
('x51xx31', '2020-09-29', NULL),
('x51xx31', '2020-09-30', NULL);
Please post DDL and follow ANSI/ISO standards when asking for help.
December 15, 2020 at 12:48 am
You also use the term "adjacent" which is a spatial concept that doesn't apply to the table. A table is an abstract unordered set of rows.
Except for HEAPs, that's not even true when it comes to physical storage, Joe. If you have a clustered index, SQL Server is compelled to place that new row on the page defined by the logical order of the index.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply