Sql query help needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sql query help needed

Author

Topic

vivek.verma111
Starting Member

6 Posts

Posted - 2010-09-09 : 10:16:34

I have 3 sql tables

Patients:

PatientID(varchar), PatientName(varchar), BPM_ID(varchar),WM_ID(varchar)
values:

2020211, XXXYYY, Philips001, ANDINS001

2002012, YDHHHH, Philips002, ANDINS002

BPMReadings:

BPM_ID(varchar), TimeStmp (datetime), BP(int)
Values:

Philips001, 09/09/2010 10:15:11, 150
Philips001, 09/09/2010 11:11:01, 152
Philips001, 08/09/2010 11:12:22, 148
Philips001, 07/09/2010 18:12:22, 147
Philips002, 09/08/2010 10:10:11, 130
etc etc

WMReadings:

WM_ID(varchar), WTimeStmp (datetime), Weight(float)

Values:

ANDINS001, 09/09/2010 09:15:11, 98
ANDINS001, 08/09/2010 10:15:11, 98.2
ANDINS001, 05/09/2010 17:14:11, 98.8
ANDINS002, 09/09/2010 12:15:11, 85
ANDINS002, 08/09/2010 18:15:11, 85.4

Need result like
query parameter is @PatientID

if @PatientID='2020211'

Query result:

Date Time BP Time Weight

09/09/2010 10:15:11 150 09:15:11 98

09/09/2010 11:11:01 152 Null Null

08/09/2010 11:12:22 148 10:15:11 98.2

07/09/2010 18:12:22 147 Null Null

05/09/2010 Null Null 17:14:11 98.8

Really appreciate if anyone can help

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-09-09 : 10:57:22

can you explain your results? How does the patient relate to the BRMReadings and WMReadings? Also, how does the BPMReadings relate to the WMReadings?

For Faster results please follow the posting guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

vivek.verma111
Starting Member

6 Posts

Posted - 2010-09-09 : 11:03:27

Hi,

In Patients table PatientID is primary key, and each patient will have a unique BPM_ID and WM_ID. The BPMReadings and WMReadings tables will have BPM_ID and WM_ID from Patients table only. There is now primary key in BPMReadings and WMReadings table as it will contain multiple readings of a patient. I need result set as shown in the post, I think it explains my question.

vivek.verma111
Starting Member

6 Posts

Posted - 2010-09-09 : 11:04:21

Typo.. *There is no Primary key in BPMReadings and WMReadings tables

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-09-09 : 12:24:52

quote:
Originally posted by vivek.verma111

I need result set as shown in the post, I think it explains my question.

If this is what you think, then why did I ask the questions? It's still not clear what your requirements are. Good luck

For Faster results please follow the posting guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-09-09 : 14:36:03

Is it too late to change your Database?

You should have a PK in every table. How about

BMPReadings - PK(PatientID, TimeStamp)

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-09-10 : 08:40:34

quote:
It's still not clear what your requirements are.

I agree. You could try explaining what the result requirements are. Someone can guess from your requirements from your example but if they guess wrong that's not helping anyone.

vivek.verma111
Starting Member

6 Posts

Posted - 2010-09-10 : 09:21:09

Ok, thanks guys for your reply. Please see this post again with all the scripts, explanation and desired result.

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/2010 10:15:11 150 09:15:11 98

10/09/2010 11:11:01 152 Null Null

09/09/2010 11:12:22 148 10:15:11 98.2

08/09/2010 18:12:22 147 Null Null

05/09/2010 Null Null 17:14:11 98.8

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-09-10 : 10:44:11

The ideas about changing your database Design may not help acheive your desired result. This is more for Normalization and Data Integrity. For example, there is nothing to prevent BPMReadings.BMP_ID to be a value that does not correspond with a Patient.BPM_ID. But if Patients.PatientID is a PK, you could have BPMReadings.PatientID be an FK (Foreign KEY) and SQL Server will enforce that it Matches a value from Patients.PatientID.

Back to your query. It might be a little tricky. You may need to create cursors for both BPM and Weights and go through each record and insert into a Temporary table then return the Temporary table. Or maybe you just want to return separtate queries for BPM and Weights and let the Application arrange the Data as you need it.

Not sure if you have flexibility but a way that would be more suited to SQL would be something like:

select TimeStamp, 'BPM' as ReadingType, BPM as Reading
from BPM, Patients P
where BMP.BMP_ID = P.BPM_ID and P.PatientID = ...
union all
select TimeStamp, 'Weight' as ReadingType, Weight as Reading
from WMReadings W, Patients P
where W.Weight_ID = P.Weight_ID and P.PatientID = ...
order by TimpStamp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources