How to generate a unique number for a datetime range?

#1

Hi. I am trying to create mock data for beds allocated for a patient.

I want to generate rows with the following attributes: id, patient_entry_time, patient_leaving_time and bed_id

I use numbers 1-60 to represent an id for each unique bed. I also record the patient entry datetime and patient leaving datetime for a patient over a period of let’s say two weeks. These are two datetime columns and I have written functions to ensure the leaving datetime is later than the patient entry datetime.

Now for each patient, I need to allocate a random bed. However, I need to ensure that only one patient uses a bed at one time. In other words, a bed shouldn’t be used by multiple patients at any moment of time over the two week period.

I would really appreciate any suggestions as to how I can achieve this desired mock data. I am hoping to generate at least 100 rows of data and ideally as many as possible.

#2

You have 14 days and 60 beds.
Take the int(rownumber/2) + 1 to get the Bed Id.
Now you can take the modulo 12 + 2 of the Bed Id to get a list of 2 - 13 for each bed. This will be the day the bed can not be occupied:

Row    Bed Id   Day not occupied
1      1        3
2      1        3
3      2        4   
4      2        4
5      3        5

Take the row and if it is even, you take a random startdate and a random enddate before the day the bed should not be occupied.
If it is odd, you randomize a start- and enddate after the day the bed should not be occupied

This will generate 120 rows with varying data without overlap.

You can create variants on it by having a different “day not occupied” algorithm.

#3

Thank you for the response. I will try and do it like this.