Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2006
    Posts
    6

    Unanswered: Elapsed time from record to record?

    Part of my job is to create timelines for hospital patient care performance review. I'd like to create a simple app to automate the sorting, calculations, and report function. That way I can simply flip through the chart recording important events and their dates and times as I find them, and the dB will put them in order, track the elapsed time between events, and print them in a readable format.

    Believe it or not, I think I've got a pretty good handle on how to use the DateDiff function to calculate the elapsed time -- which I thought might be the hard part. Instead, my problem is how to apply DateDiff to different records in the same table. See, the samples I've been able to find all have a Start and Stop time field within each record. But the events I'm tracking don't work that way: they don't start and stop; they occur in relation to one another.

    My central data entry table is very simple: {ID, Date/Time, Event}. The interval I'm interested in is therefore not found within each record (as in StartTime, EndTime), but rather from one record to the next: I need to compare the time on the first record to each subsequent record.

    Forgive me, because I know I'm missing something simple. I'm a doofus. Any guidance is much appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Pret

    Welcome to the forum

    Quote Originally Posted by Pret
    Part of my job is to create timelines for hospital patient care performance review. I'd like to create a simple app to automate the sorting, calculations, and report function. That way I can simply flip through the chart recording important events and their dates and times as I find them, and the dB will put them in order, track the elapsed time between events, and print them in a readable format.
    Ah yes - nice and simple

    Yep - your problem is that pretty well all SQL is designed to operate across rows not between them. You're not being a doofus - it isn't that simple but it is of course possible.

    How far are you through your app? You sound like you are trying to run before you can crawl. Have you designed & developed the database? Put together an interface? Is this theoretical or have you hit a brick wall in your development?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2006
    Posts
    6
    I haven't even started building tables yet; still at the conceptual stage. Again, I thought the basic tables would be easy (tblCases with patient identifiers and tblTimelines with date/times and events). It's less a matter of crawling vs. running (I'm a database hobbyist, walking with a limp and occasional assistance) than it is a chicken-egg thing: clearly I have too little SQL experience to know how my tables should best be constructed to enable this particular sort of querying.

    I've built rather elegant apps for more overtly complex tasks: organizing trauma education, contacts, feedback, performance issues, etc. By comparison I figured this would be a snap. But I can't get my brain around this simple timeline structure.

    You've suggested it wouldn't be easy: could you point me to a reference or an example I might study?

    Thanks much.

    Pret

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Pret

    No probs - I was actually doing a little bit of fishing there that the regulars will have picked up on (we try not to answer homework questions and there was a hint in my mind that this might have been one. Not anymore ).

    See what you think of the below:
    Code:
    SELECT A.ID, A.TheDate, A.TheEvent, NZ(DateDiff("D", MAX(B.TheDate), A.TheDate), "N\A")  AS TimeSinceLastEvent
    FROM MyTable AS A
     LEFT OUTER JOIN MyTable AS B ON
     A.ID = B.ID 
     AND A.TheEvent = B.TheEvent
     AND A.TheDate > B.TheDate
    GROUP BY 
     A.ID, A.TheDate, A.TheEvent
    ORDER BY
     A.TheDate
    This uses a Theta (or inequality join - the bit with the ">") self join to ensure that rows are matched by ID and Event (remove the event link if this is not relevent for your requirement) but only earlier dates.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2006
    Posts
    6
    Thanks. I think I see how it works. At least it's encouraging that I'm pretty sure the event link isn't what I'm after.

    I'll build the tables and play with it, and get back to you after I've had a chance to mess it up.

    I'm deeply grateful for the info.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - I would design your database independant of the UI and SQL first of all. Consider the design in the light of overall requirments of course but be driven by relational and normalisation principles. If you find, once you try to implement features, that you want to treak your design then fair enough.

    Basically - build from the database up not the other way round.

    Just my 0.02$
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Sep 2006
    Posts
    6
    Thanks for all of your advice. I've been tinkering with the SQL, solving some issues while creating others. Typical. But trending upward on the whole.
    What I've come up with thus far is three queries, each nested in the next (whether or not it was strictly necessary, this method has helped me keep my thoughts organized).

    The first query combines separate date and time elements from what will eventually be the UI into a single field that I can operate on (when I get to the UI, I'll have to figure out how to eliminate the need to enter a date unless it changes; but that's another topic). The first query leaves me with {ID, DateTime, Event}.

    The second query uses a slightly modified version of your SQL statement -- I changed MAX to MIN, in order to retrieve the total elapsed time, rather than the incremental; and set the format from "D" to "N" since I needed more sensitive time frames. That all yields {ID, DateTime, Event, ElapsedTime_Min}.

    The third query operates on ElapsedTime_Min to convert total minutes to "X hours, X minutes" format. Perhaps I could have just done this in the report phase; but I needed to practice my querying anyhow. This third query therefore gives me {ID, DateTime, Event, ElapsedTime}.

    This is all looking very good; but I did hit one small snag: since the third query tries to reformat the elapsed time, which in the second query is rightly allowed to be "N/A" (for the first record), it returns "#Error" under ElapsedTime for that record. I'd rather not have this show up on my report as such. Is there a workaround?

    Thanks again. I'm so glad I found this forum, and so grateful for your help.

    Pret Bjorn
    Eastern Maine Medical Center
    Bangor, ME USA

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Pret

    Glad the code helped
    Quote Originally Posted by Pret
    This is all looking very good; but I did hit one small snag: since the third query tries to reformat the elapsed time, which in the second query is rightly allowed to be "N/A" (for the first record), it returns "#Error" under ElapsedTime for that record. I'd rather not have this show up on my report as such. Is there a workaround?
    Well - you could dump the NZ(...., "N\A") bit and apply it once you have formatted the result. Or you could test the value of the field using IsNumeric() and IiF so that you only format the numeric values.

    If no - fancy posting the SQL for your second and third queries?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Sep 2006
    Posts
    6
    I'll play with both options. Back to the books -- I'm learning as I go here (took me half an hour to figure out the NZ statement. Very cool.).

    Thanks for the advice.

    Pret

    Query # 2:
    SELECT A.cID, A.DateTime, A.tDetails, NZ(DateDiff("n",MIN(B.DateTime),A.DateTime),"N/A") AS ElapsedTime_Min
    FROM qry1 AS A LEFT JOIN qry1 AS B ON (A.DateTime>B.DateTime) AND (A.cID=B.cID)
    GROUP BY A.cID, A.DateTime, A.tDetails
    ORDER BY A.DateTime;

    Query # 3:
    SELECT qry2.cID, qry2.DateTime, qry2.tDetails, [ElapsedTime_Min]\60 & " hours, " & [ElapsedTime_Min] Mod 60 & " min" AS ET
    FROM qry2;

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    One way might be:
    Code:
    SELECT qry2.cID, qry2.DateTime, qry2.tDetails, Iif(Not IsNumeric([ElapsedTime_Min]), "N\A", [ElapsedTime_Min]\60 & " hours, " & [ElapsedTime_Min] Mod 60 & " min") AS ET
    FROM qry2
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Sep 2006
    Posts
    6
    Works great, and I think I even understand it. This is a terrific learning experience.

    Now I'll refine the underlying tables and relationships, and build the UI. My longterm goal will include functionality to tag certain events on the fly, marking each as a new starting point, such that the report may measure different intervals (elapsed time since injury, at referring hospital, at trauma center...) I'm excited at the potential. This is the sort of tool that thousands of my colleagues could make good use of.

    You've given me the tools to get started, and I'll hit the books some more. I may get stuck along the way, but I'll only pester you again if I must.

    Again, you have been a tremendous help. Thanks.

    Pret

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •