locked
Best way to handle History records? RRS feed

  • Question

  • Hi everyone.

    I am interested to know if there is a better way of tracking history in the DWH i have started working in.

    Current way to track history...

    Hospital data

    Waiting list table.

    Senario:

    1 person can be on the waitinglist waiting for a operation for 100s of days and each day that goes by more are added, and some are taken away.

    When you go on the waitlist (Date applied) when your operation is performed you are off the waitlist(Date applied), when you are off the waitlist a copy of your record and the length of stay on the waitlist is applied to the WaitListHistory table.

    KPI Report senario:

    Managers then ask "Show me all people that were on the Waitlist in Jan 2010 week 3" or on a particular day.

    Current method being used.

    StoredProc:

    Rebuild the WaitlistHistory Table every day, for each record add a +1day with a date column called RunDateTime.

    Example: I was on the waitlist from 6/12/2012 and off the waitlist 13/12/2012 (7 days), the WaitlistHistory table populates this 7 times with a RunDateTime

    for each day.

    If i want to know how many people were on the waitlist on the 8/12/2012 then i count waitlistId for RundateTime 8/12/2012.

    The rebuild takes a long time (hours), the size of the table is huge.

    Another way:

    I have been thinking there could be another way to do this. So i am asking if the following can be achieved.

    We have a on the WaitlistDate and we have a off the Waitlistdate, is there a way i can take a date question and build the math on the fly..

    Example:

    On the waitlist 6/12/2012 / off the waitlist 13/12/2012

    Manager wants to know all on the waitlist on the 9/12/2012

    my idea.image below.(I dont know how to do the maths..or if this is achievable )


    David

    Wednesday, December 19, 2012 7:22 PM

Answers

  • Select....

    FROM WaitListHistoryTable

    WHERE @QuestionDate >= OnWaitlistDate AND @QuestionDate <= OffWaitlistDate

    • Marked as answer by KIWI DAVE Wednesday, December 19, 2012 9:48 PM
    Wednesday, December 19, 2012 7:44 PM
  • Of course it is doable - and even trivial in a basic sense.   Assume (since you did not provided DDL) that the wait table is: personid, add_date, remove_date.  Note this is overly simplistic table.

    To generate a report of all on the waitlist for Dec 10 2012, a simple select statement is needed:

    select * from waitlist where add_date <= '20121210' and (remove_date is null or remove_date >  '20121210');

    Given that level of simplicity, I suspect that there may be complications due to your schema. 

    • Proposed as answer by Naomi N Wednesday, December 19, 2012 8:13 PM
    • Marked as answer by KIWI DAVE Wednesday, December 19, 2012 9:48 PM
    Wednesday, December 19, 2012 7:49 PM

All replies

  • Select....

    FROM WaitListHistoryTable

    WHERE @QuestionDate >= OnWaitlistDate AND @QuestionDate <= OffWaitlistDate

    • Marked as answer by KIWI DAVE Wednesday, December 19, 2012 9:48 PM
    Wednesday, December 19, 2012 7:44 PM
  • Of course it is doable - and even trivial in a basic sense.   Assume (since you did not provided DDL) that the wait table is: personid, add_date, remove_date.  Note this is overly simplistic table.

    To generate a report of all on the waitlist for Dec 10 2012, a simple select statement is needed:

    select * from waitlist where add_date <= '20121210' and (remove_date is null or remove_date >  '20121210');

    Given that level of simplicity, I suspect that there may be complications due to your schema. 

    • Proposed as answer by Naomi N Wednesday, December 19, 2012 8:13 PM
    • Marked as answer by KIWI DAVE Wednesday, December 19, 2012 9:48 PM
    Wednesday, December 19, 2012 7:49 PM
  • Hi Scott and MC,

    When i looked at how the WaitListHistory is made, i too asked this question, why do you need to add history in this way.

    I thought there must be something else going on..

    I thought i must be simplifing things too much,

    But you have both confirmed what i thought, the person who developed the table has over complicated it.

    I asked the person why they are adding records for each day, he said "how else are you going to get a point in time output"

    anyway...

    I am greatful for your input

    :)


    David

    Wednesday, December 19, 2012 8:16 PM
  • Where is the DDL? The stuff of minimal Netiquette?  Does your boss hide DDL from you and draw pictures instead? Rows are not records; we use ISO-8601 dates in SQL; learn the basics. 

    Your approach is wrong. What you have is a patient who is going status changes over time. He is still the same kind of entity on the wait list or on the operating table. This design flaw is called attribute splitting.  Your status codes will probably be things like {admission, waiting, surgery, recovery, discharge, death, re-admit, etc} 

    Read this: http://www.simple-talk.com/sql/t-sql-programming/state-transition-constraints/

    You will need (start_date, end_date) pairs for duration spent in a state, but that is easy. 

    Since you did not post DDL, I will not DML. 


    --CELKO-- 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

    Thursday, December 20, 2012 6:13 AM