Maximum value for a field and concatenation

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)

Maximum value for a field and concatenation

Author

Topic

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2008-12-09 : 08:53:41

Hi All,

The below is the sample data from a table. My requirement is to store the maximum value of BP Max and Value of BP Min (against the BP Max).

DateofRecording ------ BP Max ------ BP Min ------------ Required Output
15-Oct-08 ------ 120 ------ 65 ------------ 150/70
15-Oct-08 ------ 130 ------ 75 ------------ 150/70
15-Oct-08 ------ 150 ------ 70 ------------ 150/70
15-Oct-08 ------ 100 ------ 120 ------------ 150/70

16-Oct-08 ------ 140 ------ 60 ------------ 140/60
16-Oct-08 ------ 120 ------ 80 ------------ 140/60
16-Oct-08 ------ 135 ------ 75 ------------ 140/60

17-Oct-08 ------ 140 ------ 80 ------------ 140/80

Thanks in advance.

Bohra

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 09:40:00

[code]SELECT DateofRecording,[BP Max] ,[BP Min],
CAST(MAX([BP Max]) OVER (PARTITION BY DateofRecording) AS varchar(10)) + CAST(MIN([BP Min]) OVER (PARTITION BY DateofRecording) AS varchar(10)) AS NewColumn
FROM YourTable[/code]

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2008-12-09 : 10:19:05

Hi Visakh,

I tried the query given by you. It is giving me maximum and minimum for the given date.
Ex: For 15-Oct-08, it is returning 150/65.
The value I want for 15-Oct-08 is 150/70.

Thanks
Bohra

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 10:28:33

quote:
Originally posted by pk_bohra

Hi Visakh,

I tried the query given by you. It is giving me maximum and minimum for the given date.
Ex: For 15-Oct-08, it is returning 150/65.
The value I want for 15-Oct-08 is 150/70.

Thanks
Bohra

then what's value you're looking at?whats the significance of 70 ?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2008-12-09 : 10:38:16

Hi Visakh,

The two columns, BP Max & BP Min stands for BloodPressure (maximum) & BloodPressure(Minimum). This two values makes a pair.
The database is of a hospital.

Doctors will be interested in knowing the lower side of blood pressure ( which belongs to maximum value).

The significance of 70 is that it is the lower side of blood pressure when the higher side was at 150.

If i show 150/65, it won't be correct as the lower side value was 70 (Value of BP Min column).

If the description is not clear then please feel free to clarify.

Thanks again,
Bohra

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 10:50:20

[code]SELECT t.DateofRecording,t.[BP Max],t.[BP Min],
CAST(t.MaxBP AS varchar(10)) + CAST(t1.[BP Min] AS varchar(10)) AS NewColumn
FROM
(
SELECT DateofRecording,[BP Max] ,[BP Min],
MAX([BP Max]) OVER (PARTITION BY DateofRecording) AS MaxBP
FROM YourTable
)t
CROSS APPLY (SELECT [BP Min]
FROM YourTable
WHERE DateofRecording=t.DateofRecording
AND [BP Max]=t. MaxBP)t1[/code]

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2008-12-09 : 10:57:07

Hi Visakh,

Your query gave me the exact solution which i was looking for.

Many many thanks to you.

Regards,
Bohra

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 10:59:38

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources