How can I exclude records from this table?

  • 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')
  • 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
    ;

    • This reply was modified 1 month, 3 weeks ago by  Jeff Moden. Reason: Added "PARTITION BY MTMRN" to handle more than one MTMRN
    • This reply was modified 1 month, 3 weeks ago by  Jeff Moden. Reason: Ask for clarification on the requirements

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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


    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • >> 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. 

  • jcelko212 32090 wrote:

    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


    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

Viewing 6 posts - 1 through 6 (of 6 total)

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