September 10, 2010 at 8:30 am
Hi, I have 3 tables see below:
Table Patient:
Create script:
CREATE TABLE [dbo].[Patient](
[PateintID] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Name] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[Address] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[BPM_ID] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[WM_ID] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED
(
[PateintID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Insert Script:
insert into Patient values ('1865004', 'S Coole', 'London', '1009822', '1122211')
insert into Patient values ('1002044', ' A Walia', 'Leicester', '1200982', '1100999')
Table BPM_Reading
Create script:
CREATE TABLE [dbo].[BPM_Reading](
[BPM_TimeStmp] [datetime] NULL,
[BPM] [int] NULL,
[BPM_ID] [varchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
Insert script:
insert into BPM_Reading values ('09-10-2010 10:15:11', '150', '1009822')
insert into BPM_Reading values ('09-10-2010 11:11:01', '152', '1009822')
insert into BPM_Reading values ('09-09-2010 11:12:22', '148', '1009822')
insert into BPM_Reading values ('09-08-2010 18:12:22', '147', '1009822')
insert into BPM_Reading values ('08-09-2010 10:10:11', '130', '1200982')
Table WM_Reading
Create script:
CREATE TABLE [dbo].[WM_Reading](
[WTimeStmp] [datetime] NULL,
[Weight] [float] NULL,
[WM_ID] [varchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
Insert script:
insert into WM_Reading values ('09-10-2010 09:15:11', '98', '1122211')
insert into WM_Reading values ('09-09-2010 10:15:11', '98.2', '1122211')
insert into WM_Reading values ('09-05-2010 17:14:11', '98.8', '1122211')
insert into WM_Reading values ('09-09-2010 12:15:11', '85', '1100999')
insert into WM_Reading values ('09-08-2010 18:15:11', '85.4', '1100999')
Requirements: Well I have some tables like these below with more columns but I have created some simple tables with less columns just to find the solution I am looking for. Patient table stores patient information and BPM_ID, WM_ID i.e. BPM and Wm ID allocated to each patient, each patient will be given a unique BPM_ID and WM_ID. Other two tables are for readings of BPM and WM at different timestamps, a patient can take any number of readings of BP and Weight. I want a query result that will display weight and BP of a individual arranged by Date. If any patient can take 2 BP readings in a day and only 1 weight reading then result of query should show 2 rows 1 with date, time, BP, time, weight and other row with date, time, BP, Null, Null. If for achieving this result I have to add any other column/index/PK I am happy with it I just want to achieve the result.
My desired query result is as:
suppose for patient S COole we take input query parameter @PatientID='1865004'
Date Time BPM Time Weight
10/09/201010:15:11 15009:15:11 98
10/09/201011:11:01 152 Null Null
09/09/201011:12:22 148 10:15:11 98.2
08/09/201018:12:22 147 Null Null
05/09/2010Null Null 17:14:11 98.8
September 10, 2010 at 9:07 am
vivek,
First of all, I want to congratulate you on the excellent job you did on posting the table/data. You'd be surprised what we have to go to in order to get others to do this.
Question: are your dates in DMY or MDY format? There's a difference between the insert statements, and the select output.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 10, 2010 at 9:25 am
How's this?
declare @PatientID varchar(50);
set @PatientID = '1865004';
WITH BP1 AS
( -- strip the time from the date
SELECT BPM_TimeStmp,
BPM,
BPM_ID,
BP_Date = DateAdd(day, DateDiff(day, 0, BPM_TimeStmp), 0)
FROM dbo.BPM_Reading
), BP2 AS
( -- get row numbers, restarting at bpm_id/bp_date
SELECT *,
RN = ROW_NUMBER() OVER (PARTITION BY BPM_ID, BP_Date ORDER BY BPM_TimeStmp DESC)
FROM BP1
),WT1 AS
( -- strip the time from the date
SELECT WTimeStmp,
[Weight],
WM_ID,
WT_Date = DateAdd(day, DateDiff(day, 0, WTimeStmp), 0)
FROM dbo.WM_Reading
), WT2 AS
( -- get row numbers, restarting at wm_id/wt_date
SELECT *,
RN = ROW_NUMBER() OVER (PARTITION BY WM_ID, WT_Date ORDER BY WTimeStmp DESC)
FROM WT1
)
-- now get everything for this patient.
SELECT BP2.BPM_TimeStmp, BP2.BPM, WT2.WTimeStmp, WT2.[Weight]
FROM dbo.Patient p
LEFT JOIN BP2
ON p.BPM_ID = BP2.BPM_ID
LEFT JOIN WT2
ON p.WM_ID = WT2.WM_ID
AND BP2.RN = WT2.RN
AND BP2.BP_Date = WT2.WT_Date
WHERE p.PateintID = @PatientID
ORDER BY BPM_TimeStmp DESC;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 11, 2010 at 11:33 am
Hi Wayne, many thanks for your reply. I am a newbie programmer and I have dealt more with programming rather then database and sql queries. In my present job I have to deal with everything and I am in learning mode of SQL, many functions you used here are new to me like this Row_Number() and partition. I am googling around and trying to learn new and new things in SQL. This query doesn't seem to work as per my requirement. It is not returning any value if there is only a weight reading for a patient and no BP readings on any given day, also see the result of the query if I change patient_id, i.e. @PatientID = '1002044' It doesn't return weight readings.
September 11, 2010 at 11:47 am
Also Wayne, I really liked your way of posting the code with comments..not everyone does like this.. It really helped in understanding the code..
September 13, 2010 at 4:56 am
I need to ask just because you're "new" to SQL. Are you 100% sure that you're using at least SQL Server 2005?
And, yeah... I agree. Wayne does an awesome job of formatting his code and explaining with comments. The forum engine "kills the green" when (-- is used, though.
Wayne... (-- won't show up as green on the forum... but (space-- does. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 13, 2010 at 5:19 am
Hi Jeff,
Thanks for your reply. Yes I am using SQL Server 2005 (I am not such a newbie to SQL though :P)
Regards,
Vivek
September 14, 2010 at 5:25 am
Jeff Moden (9/13/2010)
Wayne... (-- won't show up as green on the forum... but (space-- does. 😉
Hey... it works! Thanks Jeff.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 14, 2010 at 5:26 am
vivek.verma (9/11/2010)
Hi Wayne, many thanks for your reply. I am a newbie programmer and I have dealt more with programming rather then database and sql queries. In my present job I have to deal with everything and I am in learning mode of SQL, many functions you used here are new to me like this Row_Number() and partition. I am googling around and trying to learn new and new things in SQL. This query doesn't seem to work as per my requirement. It is not returning any value if there is only a weight reading for a patient and no BP readings on any given day, also see the result of the query if I change patient_id, i.e. @PatientID = '1002044' It doesn't return weight readings.
Whoops, I didn't see this. I'll try to look into this later today.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 14, 2010 at 7:29 am
Here is my two cents:
/*
SELECT * FROM [WM_Reading]
SELECT * FROM [BPM_Reading]
SELECT * FROM [Patient]
*/
DECLARE @PatientID INT
SET @PatientID = 1002044
; WITH BPM_CTE AS
(
SELECT -- List of Columns that we require
BPM.BPM_TimeStmp
, BPM.BPM
, BPM.BPM_ID
, Pt.PateintID
, Pt.Name
-- Make date a single date , i,e., strip off the minutes and hours
, [BPMDate] = (DATEADD(DAY, DATEDIFF(DAY, 0, BPM.BPM_TimeStmp), 0))
-- Logically order and assign a number to the rows
, BPM_RowNum = ROW_NUMBER() OVER (
PARTITION BY BPM.BPM_ID , (DATEADD(DAY, DATEDIFF(DAY, 0, BPM.BPM_TimeStmp), 0))
ORDER BY BPM.BPM_ID, (DATEADD(DAY, DATEDIFF(DAY, 0, BPM.BPM_TimeStmp), 0))
)
FROM [BPM_Reading] BPM -- Base table from where data wil be picked
INNER JOIN [Patient] Pt -- Support table where auxillary values are maintained
ON Pt.BPM_ID = BPM.BPM_ID -- Data Retreival Condition
WHERE
--Here is where we filter the PatientID with ;
-- If u want for all Patients, then comment the whole WHERE Clause along with some other comments in the
-- below code ; REad the comments of each step to check where u need to comment in case u need for all
-- Patients
Pt.PateintID = @PatientID
),
Weight_CTE AS
(
SELECT -- List of Columns that we require
WM.WTimeStmp
, WM.Weight
, WM.WM_ID
, Pt.PateintID
, Pt.Name
-- Make date a single date , i,e., strip off the minutes and hours
, [WMDate] = (DATEADD(DAY, DATEDIFF(DAY, 0, WM.WTimeStmp), 0))
-- Logically order and assign a number to the rows
, WM_RowNum = ROW_NUMBER() OVER (
PARTITION BY WM.WM_ID , (DATEADD(DAY, DATEDIFF(DAY, 0, WM.WTimeStmp), 0))
ORDER BY WM.WM_ID, (DATEADD(DAY, DATEDIFF(DAY, 0, WM.WTimeStmp), 0))
)
FROM [WM_Reading] WM -- Base table from where data wil be picked
INNER JOIN [Patient] Pt -- Support table where auxillary values are maintained
ON Pt.WM_ID = WM.WM_ID -- Data Retreival Condition
WHERE
--Here is where we filter the PatientID with ;
-- If u want for all Patients, then comment the whole WHERE Clause along with some other comments in the
-- below code ; REad the comments of each step to check where u need to comment in case u need for all
-- Patients
Pt.PateintID = @PatientID
),
FINAL AS
(
SELECT BPM.BPMDate [Day]
,CONVERT ( TIME, BPM.BPM_TimeStmp , 108) BPM_TimeStmp
,BPM.BPM
,CONVERT ( TIME, WM.WTimeStmp , 108 ) WTimeStmp
,WM.Weight
FROM BPM_CTE BPM
OUTER APPLY
(
SELECT -- List of Columns that we require
[WMDate]
,WM_RowNum
,PateintID
,WTimeStmp
,Weight
FROM Weight_CTE
UNION
(
SELECT BPMDate
, BPM_RowNum
--, PateintID -- Uncomment this if u need to do that for all the Patients
, NULL PateintID -- Comment this if u need to do that for all the Patients
, NULL WTimeStmp
, NULL Weight
FROM BPM_CTE
EXCEPT
SELECT DISTINCT
[WMDate]
, WM_RowNum
--, PateintID -- Uncomment this if u need to do that for all the Patients
, NULL -- Comment this if u need to do that for all the Patients
, NULL
, NULL
FROM Weight_CTE
)
) AS WM
WHERE
BPM.BPMDate = WM.WMDate
AND BPM.BPM_RowNum = WM.WM_RowNum
--AND BPM.PateintID = WM.PateintID -- Uncomment this if u need to do that for all the Patients
UNION
SELECT WM.WMDate [Day]
,CONVERT ( TIME, BPM.BPM_TimeStmp , 108) BPM_TimeStmp
,BPM.BPM
,CONVERT ( TIME, WM.WTimeStmp , 108 ) WTimeStmp
,WM.Weight
FROM Weight_CTE WM
OUTER APPLY
(
SELECT -- List of Columns that we require
BPMDate , BPM_RowNum , BPM_TimeStmp , BPM , PateintID
FROM BPM_CTE
UNION
(
SELECT WMDate
, WM_RowNum
--, PateintID -- Uncomment this if u need to do that for all the Patients
, NULL PateintID -- Comment this if u need to do that for all the Patients
, NULL WTimeStmp
, NULL Weight
FROM Weight_CTE
EXCEPT
SELECT DISTINCT
[BPMDate]
, BPM_RowNum
--, PateintID -- Uncomment this if u need to do that for all the Patients
, NULL -- Comment this if u need to do that for all the Patients
, NULL
, NULL
FROM BPM_CTE
)
) AS BPM
WHERE
BPM.BPMDate = WM.WMDate
AND BPM.BPM_RowNum = WM.WM_RowNum
--AND BPM.PateintID = WM.PateintID -- Uncomment this if u need to do that for all the Patients
)
SELECT * FROM FINAL
ORDER BY [Day] DESC
,BPM_TimeStmp ASC
,WTimeStmp ASC
September 14, 2010 at 7:39 am
ROFL.. LOL.. :-D... A coffee-machine break could spell disaster i guess.. FULL OUTER JOIN can accomplish that task with minimal lines of code 😀
; WITH BPM_CTE AS
(
SELECT -- List of Columns that we require
BPM.BPM_TimeStmp
, BPM.BPM
, BPM.BPM_ID
, Pt.PateintID
, Pt.Name
-- Make date a single date , i,e., strip off the minutes and hours
, [BPMDate] = (DATEADD(DAY, DATEDIFF(DAY, 0, BPM.BPM_TimeStmp), 0))
-- Logically order and assign a number to the rows
, BPM_RowNum = ROW_NUMBER() OVER (
PARTITION BY BPM.BPM_ID , (DATEADD(DAY, DATEDIFF(DAY, 0, BPM.BPM_TimeStmp), 0))
ORDER BY BPM.BPM_ID, (DATEADD(DAY, DATEDIFF(DAY, 0, BPM.BPM_TimeStmp), 0))
)
FROM [BPM_Reading] BPM -- Base table from where data wil be picked
INNER JOIN [Patient] Pt -- Support table where auxillary values are maintained
ON Pt.BPM_ID = BPM.BPM_ID -- Data Retreival Condition
),
Weight_CTE AS
(
SELECT -- List of Columns that we require
WM.WTimeStmp
, WM.Weight
, WM.WM_ID
, Pt.PateintID
, Pt.Name
-- Make date a single date , i,e., strip off the minutes and hours
, [WMDate] = (DATEADD(DAY, DATEDIFF(DAY, 0, WM.WTimeStmp), 0))
-- Logically order and assign a number to the rows
, WM_RowNum = ROW_NUMBER() OVER (
PARTITION BY WM.WM_ID , (DATEADD(DAY, DATEDIFF(DAY, 0, WM.WTimeStmp), 0))
ORDER BY WM.WM_ID, (DATEADD(DAY, DATEDIFF(DAY, 0, WM.WTimeStmp), 0))
)
FROM [WM_Reading] WM -- Base table from where data wil be picked
INNER JOIN [Patient] Pt -- Support table where auxillary values are maintained
ON Pt.WM_ID = WM.WM_ID -- Data Retreival Condition
)
SELECT COALESCE (WM.WMDate ,BPM.BPMDate)[Day]
,CONVERT ( TIME, BPM.BPM_TimeStmp , 108) BPM_TimeStmp
,BPM.BPM
,CONVERT ( TIME, WM.WTimeStmp , 108 ) WTimeStmp
,WM.Weight
FROM Weight_CTE WM
FULL OUTER JOIN BPM_CTE BPM
ON
BPM.BPMDate = WM.WMDate
AND BPM.BPM_RowNum = WM.WM_RowNum
AND BPM.PateintID = WM.PateintID
ORDER BY [Day] DESC
,BPM_TimeStmp ASC
,WTimeStmp ASC
Try the two methods and tell us which u prefer, vivek !! 😛
September 14, 2010 at 8:38 am
Hey thanks it worked like a charm and code is also too well explained, and don't know how you got it that somewhere my requirement will be data for all patients too, well for all patients today's data to display like this I achieved the result by ASP.Net/C# programming, creating temp .net data tables and comparing by their PatientID..but for a single patient's all readings comparing by date/time seemed difficult.. thanks again
and btw guess Convert(TIME, , ) function is in SQL Server 2008 and I was using 2005 so I changed the Convert(TIME, ,) with CONVERT ( VARCHAR(8), BPM.BPM_TimeStmp , 108) BPM_TimeStmp.. it worked
Thanks again 🙂
September 14, 2010 at 8:45 am
vivek.verma (9/14/2010)
@ColdCoffeeHey thanks it worked like a charm and code is also too well explained, and don't know how you got it that somewhere my requirement will be data for all patients too, well for all patients today's data to display like this I achieved the result by ASP.Net/C# programming, creating temp .net data tables and comparing by their PatientID..but for a single patient's all readings comparing by date/time seemed difficult.. thanks again
and btw guess Convert(TIME, , ) function is in SQL Server 2008 and I was using 2005 so I changed the Convert(TIME, ,) with CONVERT ( VARCHAR(8), BPM.BPM_TimeStmp , 108) BPM_TimeStmp.. it worked
Thanks again 🙂
Awesome..Happy that it worked.. 🙂
ANd sorry for not seeing that u posted it in 2005 forums, i honestly dint check the forum..sorry for that :blush:
September 14, 2010 at 8:54 am
@ ColdCoffee
The other code also worked well, and off course I will prefer less number of lines as it is somewhat good for speed. Thanks again
September 14, 2010 at 9:47 am
@cc - thanks for helping out!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply