Calculate time difference between two related visitors without using cursor

  • valeryk2000 (10/3/2016)


    We select all admissions within the defined TIME RANGE. If 'your' record is within this range (say month of August 2016) and there is another one in August 2016, and the second comes in less then 72 hrs after the first one, then we select this pair and, using ID/ArrivalDateTime, present other fields (diagnosis, hospital, attending physician, etc) to a medical professional (may be a nurse or physicians assistant) who make a decision and pick qualifying cases for their reports. 'Your' visit to the ED 3 years ago is not relevant. And your code completely satisfies these requirements.

    Thanks

    Hope it helps

    Val

    Don't forget to incorporate the "sliding window" into your production version of this. In other words - if you're trying to capture all "readmits" in August you'd have to base your selection on July 28th until August 31, 23:59:59.9999; If you're looking for all admissions causing a readmit, you'd need 8/1 through 9/4.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Absolutely - that's what we are going to do.

    Thanks

  • This is the fragment of the table

    No, this is not a table at all. A table has to have a key. But this can never have a key, because everything can be null. Putting "_table" in the table name is a design flaw called a Tibble. We really laugh at it. There is no such thing as a generic "id" in RDBMS; and identifier has to be "<something in particular>_id" and it cannot be an approximate numeric value. What math were you going to do on it? Why did you expect to find something identified by 3.141592653? This looks like a hangover from your spreadsheet. Finally, you never read an ISO paper on temporal data or basic book on data modeling. Time is a continuum, so it is modeled with the ISO half open interval model. That means in addition to having an arrival timestamp, you also need to have a departure timestamp as part of the same temporal interval.

    In SQL, we use the ISO 8601 display format for temporal data, not internal bit strings. Furthermore, the syntax for an insertion statement consist of table constructors today, not the old Sybase row by row insertions (which were used to mimic punchcards).

    The reason this is so hard for you is your design is a mess. If this were an engineering class you would be trying to build a steam powered airplane :-(:w00t:

    CREATE TABLE Readmissions

    (admission_id CHAR(10) NOT NULL PRIMARY KEY,

    arrival_timestamp DATETIME2(0) NOT NULL,

    departure_timestamp DATETIME2(0),

    CHECK (arrival_timestamp <= departure_timestamp)

    );

    Kuznetsov’s History Table gets around the weaknesses of the simple history table schema. It builds a temporal chain from the current row to the previous row. This is easier to show with code:

    CREATE TABLE Tasks

    (task_id INTEGER NOT NULL,

    task_score CHAR(1) NOT NULL,

    previous_end_date DATE, -- null means first task

    current_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,

    CONSTRAINT previous_end_date_and_current_start_in_sequence

    CHECK (prev_end_date <= current_start_date),

    current_end_date DATE, -- null means unfinished current task

    CONSTRAINT current_start_and_end_dates_in_sequence

    CHECK (current_start_date <= current_end_date),

    CONSTRAINT end_dates_in_sequence

    CHECK (previous_end_date <> current_end_date)

    PRIMARY KEY (task_id, current_start_date),

    UNIQUE (task_id, previous_end_date), -- null first task

    UNIQUE (task_id, current_end_date), -- one null current task

    FOREIGN KEY (task_id, previous_end_date) -- self-reference

    REFERENCES Tasks (task_id, current_end_date));

    Well, that looks complicated! Let’s look at it column by column. Task_id explains itself. The previous_end_date will not have a value for the first task in the chain, so it is NULL-able. The current_start_date and current_end_date are the same data elements, temporal sequence and PRIMARY KEY constraints we had in the simple history table schema.

    The two UNIQUE constraints will allow one NULL in their pairs of columns and prevent duplicates. Remember that UNIQUE is not like PRIMARY KEY, which implies UNIQUE NOT NULL.

    Finally, the FOREIGN KEY is the real trick. Obviously, the previous task has to end when the current task started for them to abut., so there is another constraint. This constraint is a self-reference that makes sure this is true. Modifying data in this type of table is easy, but requires some thought.. Fortunately, Alex has written a Simple Talk article to explain in more detail how it is done.

    Disabling Constraints

    Just one little problem with that FOREIGN KEY constraint. It will not let you put the first task into the table. There is nothing for the constraint to reference. In Standard SQL, we can declare constraints to be DEFERABLE with some other options. The idea is that you can turn a constraint ON or OFF during a session so the database can be in state that would otherwise be illegal. But at the end of the session all constraints have to be TRUE. or UNKNOWN.

    In SQL Server, you can disable constraints and then turn them back on. It actually is restricted to disabling FOREIGN KEY constraint, and CHECK constraints. PRIMARY KEY, UNIQUE, and DEFAULT constraints are always enforced. The syntax for this is part of the ALTER TABLE statement. The syntax is simple:

    ALTER TABLE <table name> NOCHECK CONSTRAINT [<constraint name> | ALL];

    This is why you want to name the constraints; without user given names, you have to look up what the system gave you and they are always long and messy.. The ALL option will disable all of the constraints in the entire schema. Be careful with it.

    To re-enable, the syntax is similar and explains itself:

    ALTER TABLE <table name> CHECK CONSTRAINT [<constraint name> | ALL];

    When a disabled constraint is re-enabled, the database does not check to ensure any of the existing data meets the constraints. So for this table, The body of a procedure to get things started would look like this:

    BEGIN

    ALTER TABLE Tasks NOCHECK CONSTRAINT ALL;

    INSERT INTO Tasks (task_id, task_score, current_start_date, current_end_date, previous_end_date)

    VALUES (1, 'A', '2010-11-01', '2010-11-03', NULL);

    ALTER TABLE Tasks CHECK CONSTRAINT ALL;

    END;

    Books in Celko Series for Morgan-Kaufmann Publishing
    Analytics and OLAP in SQL
    Data and Databases: Concepts in Practice
    Data, Measurements and Standards in SQL
    SQL for Smarties
    SQL Programming Style
    SQL Puzzles and Answers
    Thinking in Sets
    Trees and Hierarchies in SQL

  • Jason A. Long (9/27/2016)


    valeryk2000 (9/27/2016)


    Thank you. However the task may be more complicated: in some cases there is more than 2 IDs (check 10011580, 10014918, just for example, in my table posted @ 10:05:34)

    I just assumed that was just bad data and filtered it out by adding "WITH (IGNORE_DUP_KEY = ON)"...

    Aside from violating the laws of physics (it's impossible to "arrive" twice at the exact same time), it doesn't complicate anything for either of the two solutions. just remove the PK constraint and rerun it... 10501108, 11320410 & 10042522 (neither 10011580 nor 10014918 had dupe dates in your sample data :-P) will show up having TimeSincePrevDate = 0.

    I don't know the hardware the OP is running on, but the healthcare system I worked with in the past had disparate intake systems on different hardware and technology. There was a central registration database, but it didn't mean you made it into that DB *first*. It was one of those "eventual consistency" scenarios: you could get registered into two different systems in the ER depending on whether you *walked* into the ER or you were *wheeled* into the ER from an ambulance.

    The laws of physics don't change but the data issues caused by those system interactions sure yielded some odd requirements.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Matt Miller (#4)

    Don't forget to incorporate the "sliding window" into your production version of this. In other words - if you're trying to capture all "readmits" in August you'd have to base your selection on July 28th until August 31, 23:59:59.9999; If you're looking for all admissions causing a readmit, you'd need 8/1 through 9/4.

    Actually, 7/28 to 9/4.

    To register a re-admission on 1 Aug you need to be aware of a corresponding admission in late July.

    _____________
    Code for TallyGenerator

  • CELKO: Do not understand - is it about script from my post of Posted 9/27/2016 10:05:34 PM? This is just a result of query that I originally sent as an Excel file. This is not the way we design tables.

    Thank you

  • Sergiy: Sure. We'll take care of the dates. The report is not supposed to run on monthly basis. So the dates should be flexible including +/- 3 days. With some reservations.

  • Sergiy (10/5/2016)


    Matt Miller (#4)

    Don't forget to incorporate the "sliding window" into your production version of this. In other words - if you're trying to capture all "readmits" in August you'd have to base your selection on July 28th until August 31, 23:59:59.9999; If you're looking for all admissions causing a readmit, you'd need 8/1 through 9/4.

    Actually, 7/28 to 9/4.

    To register a re-admission on 1 Aug you need to be aware of a corresponding admission in late July.

    Right - but you usually are counting either the earlier admit OR the latter, not both at the same time. Same idea though.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Matt Miller (#4) (10/5/2016)


    Sergiy (10/5/2016)


    Matt Miller (#4)

    Don't forget to incorporate the "sliding window" into your production version of this. In other words - if you're trying to capture all "readmits" in August you'd have to base your selection on July 28th until August 31, 23:59:59.9999; If you're looking for all admissions causing a readmit, you'd need 8/1 through 9/4.

    Actually, 7/28 to 9/4.

    To register a re-admission on 1 Aug you need to be aware of a corresponding admission in late July.

    Right - but you usually are counting either the earlier admit OR the latter, not both at the same time. Same idea though.

    May be yrs, may be no.

    It depends.

    Main thing - OP has the correct understanding and in on the right track.

    _____________
    Code for TallyGenerator

  • Sergiy (10/6/2016)


    Matt Miller (#4) (10/5/2016)


    Sergiy (10/5/2016)


    Matt Miller (#4)

    Don't forget to incorporate the "sliding window" into your production version of this. In other words - if you're trying to capture all "readmits" in August you'd have to base your selection on July 28th until August 31, 23:59:59.9999; If you're looking for all admissions causing a readmit, you'd need 8/1 through 9/4.

    Actually, 7/28 to 9/4.

    To register a re-admission on 1 Aug you need to be aware of a corresponding admission in late July.

    Right - but you usually are counting either the earlier admit OR the latter, not both at the same time. Same idea though.

    May be yrs, may be no.

    It depends.

    Main thing - OP has the correct understanding and in on the right track.

    A caveat: including earlier than '1st of August' admissions would add new ID/ArrivalDateTime that might be taken care of. So to use the 'latter' approach seems to be more appropriate (or not?)

  • CELKO (10/5/2016)


    This is the fragment of the table

    No, this is not a table at all. A table has to have a key.

  • CELKO (10/5/2016)


    This is the fragment of the table

    No, this is not a table at all. A table has to have a key.

    To further clarify: ID and ArrivalDateTime is a composite primary keys. Create table script was implicitly generated by SS.

  • valeryk2000 (10/6/2016)


    Sergiy (10/6/2016)


    Matt Miller (#4) (10/5/2016)


    Sergiy (10/5/2016)


    Matt Miller (#4)

    Don't forget to incorporate the "sliding window" into your production version of this. In other words - if you're trying to capture all "readmits" in August you'd have to base your selection on July 28th until August 31, 23:59:59.9999; If you're looking for all admissions causing a readmit, you'd need 8/1 through 9/4.

    Actually, 7/28 to 9/4.

    To register a re-admission on 1 Aug you need to be aware of a corresponding admission in late July.

    Right - but you usually are counting either the earlier admit OR the latter, not both at the same time. Same idea though.

    May be yrs, may be no.

    It depends.

    Main thing - OP has the correct understanding and in on the right track.

    A caveat: including earlier than '1st of August' admissions would add new ID/ArrivalDateTime that might be taken care of. So to use the 'latter' approach seems to be more appropriate (or not?)

    It all depends on the question you're trying to answer. in my previous workplace, we ended up using both options to look at different things: the "earlier was use to profile which cases were likely to readmit, and review if three was something that could have been done differently or were the discharge criteria too aggressive, etc....

    The "later" was used to review whether/in what way the condition had changed from the initial readmit. To some degree they ended up using this as well to really validate what was a true readmit (i.e. a continuation of the SAME episode) as opposed to a secondary occurrence which may not be a continuation of the prior one..

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • valeryk2000 (10/5/2016)


    CELKO: Do not understand - is it about script from my post of Posted 9/27/2016 10:05:34 PM? This is just a result of query that I originally sent as an Excel file. This is not the way we design tables.

    Thank you

    Joe is what we would call a prescriptivist in linguistics. He has very strong ideas about how SQL should be implemented which are not always practical or even possible within a specific implementation of SQL (e.g. T-SQL). Anything Joe says is about this idealized version of SQL and you need to determine how applicable it is to the real-world situation that you are facing.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen (10/6/2016)


    valeryk2000 (10/5/2016)


    CELKO: Do not understand - is it about script from my post of Posted 9/27/2016 10:05:34 PM? This is just a result of query that I originally sent as an Excel file. This is not the way we design tables.

    Thank you

    Joe is what we would call a prescriptivist in linguistics. He has very strong ideas about how SQL should be implemented which are not always practical or even possible within a specific implementation of SQL (e.g. T-SQL). Anything Joe says is about this idealized version of SQL and you need to determine how applicable it is to the real-world situation that you are facing.

    Drew

    I see ... Well ... I think that it's always good to be reminded of how an ideal SQL should be implemented.

    Thank you.

Viewing 15 posts - 31 through 45 (of 52 total)

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