May 16, 2012 at 7:23 am
Hi everyone, I am a newbie with SQL Server and T-SQL. I have got a problem and appreciate if anyone can give the best way to do this. There are two tables- Patients and Observations: Create scripts:
Patients table:
CREATE TABLE [dbo].[Patients](
[PatientID] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[PatientName] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
(
[PatientID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Observations table:
CREATE TABLE [dbo].[Observations](
[ObservationID] [int] NOT NULL,
[DeviceID] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[PatientID] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[MeasurementType] [int] NULL,
[MeasurementValue] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[MeasurementTime] [datetime] NULL,
CONSTRAINT [PK_Observations] PRIMARY KEY CLUSTERED
(
[ObservationID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
GO
ALTER TABLE [dbo].[Observations] WITH CHECK ADD CONSTRAINT [FK_Observations_Patients] FOREIGN KEY([PatientID])
REFERENCES [dbo].[Patients] ([PatientID])
GO
ALTER TABLE [dbo].[Observations] CHECK CONSTRAINT [FK_Observations_Patients]
Please insert these, insert scripts below:
Patients table:
INSERT INTO [Patients] ([PatientID],[PatientName])VALUES('1234','John Doe')
INSERT INTO [Patients] ([PatientID],[PatientName])VALUES('1235','Jane Doe')
INSERT INTO [Patients] ([PatientID],[PatientName])VALUES('1236','David Doe')
INSERT INTO [Patients] ([PatientID],[PatientName])VALUES('1237','Mark Doe')
INSERT INTO [Patients] ([PatientID],[PatientName])VALUES('1238','Ravi Doe')
Observations table:
INSERT INTO [Observations] ([ObservationID],[DeviceID],[PatientID],[MeasurementType],[MeasurementValue],[MeasurementTime])VALUES(1,'0ASF4450','1234',1,'85','May 16 2012 9:00:00:000AM')
INSERT INTO [Observations] ([ObservationID],[DeviceID],[PatientID],[MeasurementType],[MeasurementValue],[MeasurementTime])VALUES(2,'0DFSDFDS','1234',3,'77','May 16 2012 9:10:00:000AM')
INSERT INTO [Observations] ([ObservationID],[DeviceID],[PatientID],[MeasurementType],[MeasurementValue],[MeasurementTime])VALUES(3,'0DFSDFDS','1234',1,'86','May 16 2012 10:10:00:000AM')
INSERT INTO [Observations] ([ObservationID],[DeviceID],[PatientID],[MeasurementType],[MeasurementValue],[MeasurementTime])VALUES(4,'0SDFSFEE','1236',1,'65','May 16 2012 9:10:00:000AM')
INSERT INTO [Observations] ([ObservationID],[DeviceID],[PatientID],[MeasurementType],[MeasurementValue],[MeasurementTime])VALUES(5,'0SDFSDW','1234',2,'12.2','May 16 2012 11:00:00:000AM')
INSERT INTO [Observations] ([ObservationID],[DeviceID],[PatientID],[MeasurementType],[MeasurementValue],[MeasurementTime])VALUES(6,'OSHHW1','1236',2,'22','May 15 2012 1:21:00:000AM')
Requirement is that to display all patient's observations for a given date, if any patient has no observations of any measurement type then show only his name, ID and rest values null. If any patient has 2 or more readings of same measurement type on a given date then create another row as shown above. Result set is ordered by measurement time descending. There are only 4 measurement types- 1, 2, 3 and 4
Expected result set for date 16/05/2010:
PatientID PatientName Time Measurementtype=1Value Time MeasurementType=2Value Time MeasurementType=3Value Time MeasurementType=4VALUE
1234 John Doe 10:10 86 11:00 12.2 09:10 77 NULLNULL
1234 John Doe 09:00 85 Null NullNullNullNullNull
1236 David DOe 10:00 65 NullNullNullNullNullNull
1235 Jane Doe NullNull NullNullNullNullNullNull
1237 Mark Doe NullNull NullNullNullNullNullNull
1238 Ravi Doe NullNull NullNullNullNullNullNull
May 16, 2012 at 7:32 am
Thanks for posting all of the pertinent information! Since you are learning, I don't want to simply provide you with a script. So, are you familiar with a left join? That is what should be used here; i.e. it will show all patients, regardless of an observation, and will return nulls if there are no observations.
Jared
CE - Microsoft
May 16, 2012 at 7:50 am
Hi,
Thanks for your reply. Yes I am familiar with Joins, I tried many ways, one of them is:
With MeasurementType1 As
(Select * from Observations where measurementtype=1 and date=.........)
measurementtype2 AS
(
...........)
...............
then joining each of them with their patientID, used row_number over partition by.... then in the end join with Patients table.. this worked if I have all the measurementtypes, but when suppose there is no measurement type=1 for a patient and it has measurementtype2 so unable to join them because will not get patientID from one of there is no data. Actually I am dealing with large tables and lots of relationships I have tried this method but I am not getting the result set I expect. So in the end I made a smaller version of the database I am working with and posted here to see people's solutions.
Regards
May 16, 2012 at 8:10 am
Ahh yes... Sorry, I did not look at your results completely. You may want to look at the PIVOT capabilities of SQL Server. Personally, I prefer to picot the data AFTER retrieving it. Either in Reporting Services or in Excel. So I would simply start with a LEFT JOIN to get all of the data needed in a result set where the type is still in a column, then PIVOT on that result set.
SELECT p.PatientID, p.PatientName, o.Time, o.Measurementtype, o.Value
FROM patients p
LEFT JOIN observations o
ON p.patientID = o.patientID
WHERE o.time > 'whatever' AND o.time < 'whateverelse'
Then pivot.
Jared
CE - Microsoft
May 16, 2012 at 8:42 am
Hmmm, it's not quite as simple as a pivot I think.
Try this: -
SELECT [PatientID], [PatientName], [Time1] AS [Time], [Measurementtype=1Value], [Time2] AS [Time],
[Measurementtype=2Value], [Time3] AS [Time], [Measurementtype=3Value], [Time4] AS [Time],
[Measurementtype=4Value]
FROM (SELECT [PatientID], [PatientName], pos,
MAX(CASE WHEN [MeasurementType] = 1 THEN [MeasurementTime] ELSE NULL END) AS [Time1],
MAX(CASE WHEN [MeasurementType] = 1 THEN [MeasurementValue] ELSE NULL END) AS [Measurementtype=1Value],
MAX(CASE WHEN [MeasurementType] = 2 THEN [MeasurementTime] ELSE NULL END) AS [Time2],
MAX(CASE WHEN [MeasurementType] = 2 THEN [MeasurementValue] ELSE NULL END) AS [Measurementtype=2Value],
MAX(CASE WHEN [MeasurementType] = 3 THEN [MeasurementTime] ELSE NULL END) AS [Time3],
MAX(CASE WHEN [MeasurementType] = 3 THEN [MeasurementValue] ELSE NULL END) AS [Measurementtype=3Value],
MAX(CASE WHEN [MeasurementType] = 4 THEN [MeasurementTime] ELSE NULL END) AS [Time4],
MAX(CASE WHEN [MeasurementType] = 4 THEN [MeasurementValue] ELSE NULL END) AS [Measurementtype=4Value]
FROM (SELECT pat.[PatientID], pat.[PatientName], obs.[MeasurementType], obs.[MeasurementTime],
obs.[MeasurementValue],
ROW_NUMBER() OVER(PARTITION BY pat.[PatientID], obs.[MeasurementType] ORDER BY obs.[MeasurementTime])
FROM [dbo].[Patients] pat
LEFT OUTER JOIN [dbo].[Observations] obs ON pat.PatientID = obs.PatientID
) firstHit([PatientID],[PatientName],[MeasurementType],[MeasurementTime],[MeasurementValue],pos)
GROUP BY [PatientID], [PatientName], pos
) secondHit
ORDER BY PatientID;
Returns: -
PatientID PatientName Time Measurementtype=1Value Time Measurementtype=2Value Time Measurementtype=3Value Time Measurementtype=4Value
-------------------------------------------------- -------------------------------------------------- ----------------------- -------------------------------------------------- ----------------------- -------------------------------------------------- ----------------------- -------------------------------------------------- ----------------------- --------------------------------------------------
1234 John Doe 2012-05-16 09:00:00.000 85 2012-05-16 11:00:00.000 12.2 2012-05-16 09:10:00.000 77 NULL NULL
1234 John Doe 2012-05-16 10:10:00.000 86 NULL NULL NULL NULL NULL NULL
1235 Jane Doe NULL NULL NULL NULL NULL NULL NULL NULL
1236 David Doe 2012-05-16 09:10:00.000 65 2012-05-15 01:21:00.000 22 NULL NULL NULL NULL
1237 Mark Doe NULL NULL NULL NULL NULL NULL NULL NULL
1238 Ravi Doe NULL NULL NULL NULL NULL NULL NULL NULL
May 16, 2012 at 9:21 am
Hi Cadavre
Thanks for this, it seems like I am going somewhere now with this way.. and yes it is not simple to use pivot with this.. btw I think you didnt see:
Requirement is that to display all patient's observations for a given date
Expected result set for date 16/05/2010:
Result set is ordered by measurement time descending
If I add this to your internal most query:
SELECT pat.[PatientID], pat.[PatientName], obs.[MeasurementType],
obs.[MeasurementTime],obs.[MeasurementValue],
ROW_NUMBER() OVER(PARTITION BY pat.[PatientID], obs.[MeasurementType]
ORDER BY obs.[MeasurementTime])
FROM [dbo].[Patients] pat
LEFT OUTER JOIN [dbo].[Observations] obs ON pat.PatientID = obs.PatientID
WHERE datediff( d, MeasurementTime, getdate())=0
It will leave patients with no observations.. anyway let me look at it properly and find out something
May 16, 2012 at 9:25 am
SQLKnowItAll (5/16/2012)
Personally, I prefer to picot the data AFTER retrieving it. Either in Reporting Services or in Excel.
Hi Jared - You don't happen to have a link to an article that explains how to pivot in SSRS, do you? I've looked around online, but haven't come up with anything.
May 16, 2012 at 9:29 am
DataAnalyst110 (5/16/2012)
SQLKnowItAll (5/16/2012)
Personally, I prefer to picot the data AFTER retrieving it. Either in Reporting Services or in Excel.Hi Jared - You don't happen to have a link to an article that explains how to pivot in SSRS, do you? I've looked around online, but haven't come up with anything.
You just take the column and place it into the "columns" section of a tablix instead of data or rows.
Jared
CE - Microsoft
May 16, 2012 at 9:30 am
vickys (5/16/2012)
Hi CadavreThanks for this, it seems like I am going somewhere now with this way.. and yes it is not simple to use pivot with this.. btw I think you didnt see:
Requirement is that to display all patient's observations for a given date
Expected result set for date 16/05/2010:
Result set is ordered by measurement time descending
If I add this to your internal most query:
SELECT pat.[PatientID], pat.[PatientName], obs.[MeasurementType],obs.[MeasurementTime],obs.[MeasurementValue],
ROW_NUMBER() OVER(PARTITION BY pat.[PatientID], obs.[MeasurementType]
ORDER BY obs.[MeasurementTime])
FROM [dbo].[Patients] pat
LEFT OUTER JOIN [dbo].[Observations] obs ON pat.PatientID = obs.PatientID
WHERE datediff( d, MeasurementTime, getdate())=0
It will leave patients with no observations.. anyway let me look at it properly and find out something
You're right, I did miss that. But what you've done won't work, you need to limit the Observations table in a subquery.
Try this: -
SELECT [PatientID], [PatientName], [Time1] AS [Time], [Measurementtype=1Value], [Time2] AS [Time],
[Measurementtype=2Value], [Time3] AS [Time], [Measurementtype=3Value], [Time4] AS [Time],
[Measurementtype=4Value]
FROM (SELECT [PatientID], [PatientName], pos,
MAX(CASE WHEN [MeasurementType] = 1 THEN [MeasurementTime] ELSE NULL END) AS [Time1],
MAX(CASE WHEN [MeasurementType] = 1 THEN [MeasurementValue] ELSE NULL END) AS [Measurementtype=1Value],
MAX(CASE WHEN [MeasurementType] = 2 THEN [MeasurementTime] ELSE NULL END) AS [Time2],
MAX(CASE WHEN [MeasurementType] = 2 THEN [MeasurementValue] ELSE NULL END) AS [Measurementtype=2Value],
MAX(CASE WHEN [MeasurementType] = 3 THEN [MeasurementTime] ELSE NULL END) AS [Time3],
MAX(CASE WHEN [MeasurementType] = 3 THEN [MeasurementValue] ELSE NULL END) AS [Measurementtype=3Value],
MAX(CASE WHEN [MeasurementType] = 4 THEN [MeasurementTime] ELSE NULL END) AS [Time4],
MAX(CASE WHEN [MeasurementType] = 4 THEN [MeasurementValue] ELSE NULL END) AS [Measurementtype=4Value]
FROM (SELECT pat.[PatientID], pat.[PatientName], obs.[MeasurementType], obs.[MeasurementTime],
obs.[MeasurementValue], obs.pos
FROM [dbo].[Patients] pat
LEFT OUTER JOIN (SELECT [MeasurementValue], [MeasurementType], [MeasurementTime], [PatientID],
ROW_NUMBER() OVER(PARTITION BY [PatientID], [MeasurementType] ORDER BY [MeasurementTime]) AS pos
FROM [dbo].[Observations]
WHERE [MeasurementTime] >= DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
AND [MeasurementTime] < DATEADD(dd, DATEDIFF(dd, 0, getdate()), 1)) obs ON pat.[PatientID] = obs.[PatientID]
) firstHit
GROUP BY [PatientID], [PatientName], pos
) secondHit
ORDER BY PatientID;
Returns: -
PatientID PatientName Time Measurementtype=1Value Time Measurementtype=2Value Time Measurementtype=3Value Time Measurementtype=4Value
-------------------------------------------------- -------------------------------------------------- ----------------------- -------------------------------------------------- ----------------------- -------------------------------------------------- ----------------------- -------------------------------------------------- ----------------------- --------------------------------------------------
1234 John Doe 2012-05-16 09:00:00.000 85 2012-05-16 11:00:00.000 12.2 2012-05-16 09:10:00.000 77 NULL NULL
1234 John Doe 2012-05-16 10:10:00.000 86 NULL NULL NULL NULL NULL NULL
1235 Jane Doe NULL NULL NULL NULL NULL NULL NULL NULL
1236 David Doe 2012-05-16 09:10:00.000 65 NULL NULL NULL NULL NULL NULL
1237 Mark Doe NULL NULL NULL NULL NULL NULL NULL NULL
1238 Ravi Doe NULL NULL NULL NULL NULL NULL NULL NULL
SELECT [PatientID], [PatientName], [Time1] AS [Time], [Measurementtype=1Value], [Time2] AS [Time],
[Measurementtype=2Value], [Time3] AS [Time], [Measurementtype=3Value], [Time4] AS [Time],
[Measurementtype=4Value]
FROM (SELECT [PatientID], [PatientName], pos,
MAX(CASE WHEN [MeasurementType] = 1 THEN [MeasurementTime] ELSE NULL END) AS [Time1],
MAX(CASE WHEN [MeasurementType] = 1 THEN [MeasurementValue] ELSE NULL END) AS [Measurementtype=1Value],
MAX(CASE WHEN [MeasurementType] = 2 THEN [MeasurementTime] ELSE NULL END) AS [Time2],
MAX(CASE WHEN [MeasurementType] = 2 THEN [MeasurementValue] ELSE NULL END) AS [Measurementtype=2Value],
MAX(CASE WHEN [MeasurementType] = 3 THEN [MeasurementTime] ELSE NULL END) AS [Time3],
MAX(CASE WHEN [MeasurementType] = 3 THEN [MeasurementValue] ELSE NULL END) AS [Measurementtype=3Value],
MAX(CASE WHEN [MeasurementType] = 4 THEN [MeasurementTime] ELSE NULL END) AS [Time4],
MAX(CASE WHEN [MeasurementType] = 4 THEN [MeasurementValue] ELSE NULL END) AS [Measurementtype=4Value]
FROM (SELECT pat.[PatientID], pat.[PatientName], obs.[MeasurementType], obs.[MeasurementTime],
obs.[MeasurementValue], obs.pos
FROM [dbo].[Patients] pat
LEFT OUTER JOIN (SELECT [MeasurementValue], [MeasurementType], [MeasurementTime], [PatientID],
ROW_NUMBER() OVER(PARTITION BY [PatientID], [MeasurementType] ORDER BY [MeasurementTime] DESC) AS pos
FROM [dbo].[Observations]
WHERE [MeasurementTime] >= DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
AND [MeasurementTime] < DATEADD(dd, DATEDIFF(dd, 0, getdate()), 1)) obs ON pat.[PatientID] = obs.[PatientID]
) firstHit
GROUP BY [PatientID], [PatientName], pos
) secondHit
ORDER BY PatientID;
Returns: -
PatientID PatientName Time Measurementtype=1Value Time Measurementtype=2Value Time Measurementtype=3Value Time Measurementtype=4Value
-------------------------------------------------- -------------------------------------------------- ----------------------- -------------------------------------------------- ----------------------- -------------------------------------------------- ----------------------- -------------------------------------------------- ----------------------- --------------------------------------------------
1234 John Doe 2012-05-16 10:10:00.000 86 2012-05-16 11:00:00.000 12.2 2012-05-16 09:10:00.000 77 NULL NULL
1234 John Doe 2012-05-16 09:00:00.000 85 NULL NULL NULL NULL NULL NULL
1235 Jane Doe NULL NULL NULL NULL NULL NULL NULL NULL
1236 David Doe 2012-05-16 09:10:00.000 65 NULL NULL NULL NULL NULL NULL
1237 Mark Doe NULL NULL NULL NULL NULL NULL NULL NULL
1238 Ravi Doe NULL NULL NULL NULL NULL NULL NULL NULL
May 16, 2012 at 9:43 am
But what you've done won't work, you need to limit the Observations table in a subquery.
Ahh yes.. Thank you very much
May 16, 2012 at 9:53 am
Cadavre seemed to grasp what you needed much quicker than I. Kudos! Can I ask, though, what is the purpose of this data. I ask because in your first example you have a patient with 2 occurrences of the same type on the same day. You also have 1 occurrence of measurement type 2 and 3 for that date. So, as a physician or a business manager:
PatientID PatientName Time Measurementtype=1Value Time MeasurementType=2Value Time MeasurementType=3Value Time MeasurementType=4VALUE
1234 John Doe 10:10 86 11:00 12.2 09:10 77 NULLNULL
1234 John Doe 09:00 85 Null NullNullNullNullNull
This is very confusing to me. I would ask what is the reason for displaying the data this way? I'm not assuming you have no reason to do this, I am just curious. Reports typically are written to paint a picture, and this does not do that for this patient. For example, why can't the type 1 09:00 85 be on the first row and the 10:10 86 be on the second? If it doesn't matter, then this report doesn't really describe much.
Jared
CE - Microsoft
May 16, 2012 at 11:05 am
Hi Jared,
There is a single observations table with these columns I listed here and some more columns. The database is connected to a WS.. the web service accepts HL7 data that is the data sent by different medical devices say BP, Glucose, WS, etc.. lots.. this table is connected to different other tables like Devices, MeasurementTypecodes, ValueTypes etc...
Now there is a healthcare portal that physician will use to monitor patients.. Default page will show today's data from all the patients.. sometimes a person takes 2-3 readings in a day, sometimes he misses taking readings from some devices etc etc.. We then show alerts where he has missed, send text message etc.. Hope this gave you a clear picture.
Regards,
Vicky
May 16, 2012 at 11:13 am
Ok, so in the example I referenced... Is there any relation between the first Type 1 measure to the Type 2 and Type 3 that makes it appear in the same row that the second Type 1 measurement does not share? Or, does it not matter which Type 1 record appears with the Type 2 and Type 3 as long as it is the same date and the two Type 1's are in separate rows?
Jared
CE - Microsoft
May 16, 2012 at 11:42 am
There is no relation, idea is to display the recent data first row.. So if there are two type 1 and type 2 measurements at 10:00 and 9:00 each then first row will have data of 10 AM for both type1 and type 2 and 2nd row will have data of 9AM.. if a type 3 measurement comes in at say 9:30 idea is to add it to first row...
May 16, 2012 at 11:53 am
Ok, so it is more like 4 columns that have events in them where the data between columns is not necessarily related except by date and patient.However, just because a Type 1, Type 2, and Type 3 appear in the same row, it is only because that is how SQL represents it. I.e. It would be "logical" to display the data as:
PatientID PatientName Time Measurementtype=1Value Time MeasurementType=2Value Time MeasurementType=3Value Time MeasurementType=4VALUE
1234 John Doe 10:10, 09:00 86 , 85 11:00 12.2 09:10 77 NULLNULL
Indicating that there is really only 1 grouping (date and patient).
You see, for display purposes I can see why you are doing this. IMHO, however, from a set I think that all elements in a row are related in some way that they are not related to another row. In this case, that is not true. I'm not sure if this solution will cause you issues in the future because the result set is not really being treated as such, but as a display mechanism. Just my 2-cents.
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply