Solved

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

Posted on 2011-09-21
2
300 Views
Last Modified: 2012-05-12
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 Expected table result
0
Comment
Question by:Warfarin162
2 Comments
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 36575728
One way without need for generating intermediate pseudo-records is to use an outer join, using the time. However, that requires that probes are taken exactly on the same time, if taken. We can remove that restriction by e.g. cutting off the seconds, to make it more fuzzy.

First, you build a select for each particular vital sign - you will certainly have an ID to differ between blood pressure and pulse aso, and I will assume it is called "type".
Then we use those selects as tables, and join them together:
select at.patientID, coalesce(bp.time, p.time) as Time, bp.value as [Blood Pressure], p.value as [Pulse]
from #AnesthesiaTimes at
left join (select patientID, time, value from signals where type = 'Blood Pressure') bp
  on at.patientID = bp.patientID and bp.time between at.AnesthesiaStartTime and at.AnesthesiaEndTime
left join (select patientID, time, value from signals where type = 'Pulse') p
  on at.patientID = p.patientID  and  p.time between at.AnesthesiaStartTime and at.AnesthesiaEndTime
 and bp.time = p.time 

Open in new window

You should get rid of seconds, if needed, if we change
  and bp.time = p.time

Open in new window

to
  and bp.time - datepart(ss, bp.time) = p.time - datepart(ss, p.time)

Open in new window

0
 

Author Comment

by:Warfarin162
ID: 36575846
Coalesce!  This works perfectly!

Thanks for teaching me a cool new trick :)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now