none
Add records where no record is captured (hospital data)

    Question

  • Hi,

    I am going to start by explaining the problem, if anyone has run into this type of problem then any advise would be appreciated.

    Our hospital system captures occupancy of wards.

    example: Ward A has 5 beds, each day for week1 each bed has a patient, this is entered into the hospital system, then we count the Occupancy of Ward A for week1. = 5

    Ward A on Week2 has 5 beds and no beds are Occupied by patients, this is NOT entered into the hospital system.

    So when we go to count the Occupancy for the 2 week period we are unable to COUNT 0 for Week2 as no physical record has been entered into the system that says 0 or NULL

    Yes our Total for Week1-2 = 5 but we need to see Week2 and the NULL record.

    What would be ideal is the source hospital system regardless of a bed being occupied or not is added to the system, but this will not happen.

    I have to create the additonal record.

    If anyone is interested in helping then i will post more.

    thanks :)


    David

    Wednesday, June 19, 2013 2:15 AM

Answers

  • Hi David

    Are you copy these occupancy records to a data warehouse, or is this required for an sql query (e.g. report)?

    If you are doing data warehousing, you could have the ETL process create rows with zero occupancy for each ward for each week and then it just gets updated if there is actually an occupancy value.

    if you need this returned in a query, there are many approaches. One is to cross join the wards with the weeks and return a Zero if there are no occupancy values.

    I hope this helps, but you need to provide more information for me to be able to give a good answer

    Regards,


    Craig Bryden - Please mark correct and/or helpful answers

    • Marked as answer by KIWI DAVE Friday, June 21, 2013 3:06 AM
    Wednesday, June 19, 2013 3:21 AM

All replies

  • Hi David

    Are you copy these occupancy records to a data warehouse, or is this required for an sql query (e.g. report)?

    If you are doing data warehousing, you could have the ETL process create rows with zero occupancy for each ward for each week and then it just gets updated if there is actually an occupancy value.

    if you need this returned in a query, there are many approaches. One is to cross join the wards with the weeks and return a Zero if there are no occupancy values.

    I hope this helps, but you need to provide more information for me to be able to give a good answer

    Regards,


    Craig Bryden - Please mark correct and/or helpful answers

    • Marked as answer by KIWI DAVE Friday, June 21, 2013 3:06 AM
    Wednesday, June 19, 2013 3:21 AM

  • What would be ideal is the source hospital system regardless of a bed being occupied or not is added to the system, but this will not happen.


    Hi,

    From the same table why can't you maintain FLAG LIKE 1 OR 0. If Bed has been accupied then 1 else 0.

     WARD_ID INT,
     WARD_DESC VARCHAR(100),
     NO_OF_BEDS INT,
     OCCUPANCY_FLAG BIT

    Thanks.


    bala krishna

    Wednesday, June 19, 2013 4:33 AM
  • Hi Craig,

    Ok i will go and have a look at this in a SSIS package, can i come back to you with a result or more info from you

    thanks

    :)


    David

    Thursday, June 20, 2013 4:20 AM