Link to home
Start Free TrialLog in
Avatar of Warfarin162
Warfarin162

asked on

Creating a row of data for every minute between two known dates

I am working in a medical database.  I want to summarize multiple vital signs (blood pressure, pulse, oxygen saturation, etc) into a single table, that show the values for each minute in a new row.  (Our EHR populates a value once a minute max).  The data is currently stored as ROWS in the table "Signals".... I am interested in grouping some vitals that occurred at the same time (putting results into COLUMNS).

The issue:  I can't simply use the timestamp of any given vital sign to connect the other vitals from that minute together, because no single vital sign is ALWAYS documented in every minute.  For instance, blood pressure is usually every 5 minutes, while pulse is usually every 1 minute (unless there is an issue with the monitor......).

I think if I can create a column of datetimes with 1 minute between each one, I could then connect the other vital signs at a mimute to the same row, since "PatientID" and "Time" could combine into a unique key for the added parameter.  The 1 minute intervals need to START at "AnesthesiaStartTime", and END at "AnesthesiaEndTime"

#AnesthesiaTimes.AnesthesiaStartTime - Defined in a temporary table

#AnesthesiaTimes.AnesthesiaEndTime - Defined in a temporary table


Final table will hopefully look something like the image attached (with more values tagged on to the end)

The items of interest are all found in the table "Signals"
Signals.PatientID
Signals.Time (when the vital sign is charted)
Signals.Value User generated image
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Warfarin162
Warfarin162

ASKER

Coalesce!  This works perfectly!

Thanks for teaching me a cool new trick :)