-----------------------------------------------------------
New Message on BDOTNET
-----------------------------------------------------------
From: VinodK [TLabs, SCT]
Message 2 in Discussion
Hi Shrinath, Well the requirement like these can make you think is it still
possible. Well, there are two approaches here. 1. You can write an CASE statement
and make the comparision and get the MAX and Min Values. But this is easy if you have
just three values ... Gets complex when the number of columns in the row increase ...
2. Use a User Defined Function (UDF) to do the comparision for you. This is the best
way around for your problem. Create table temp1 (Supplier Varchar(30), QR1
decimal(3,1), QR2 decimal(3,1), QR3 decimal(3,1), QR4 decimal(3,1)) Insert into Temp1
values ('ABC' , 2.3, 2.8, 2.4, 1.1)
Insert into Temp1 values ('XYZ' , 2.1, 2.5, 2.6, 2.1) GO Create function dbo.SendMax
(@QR1 decimal(3,1), @QR2 decimal(3,1),
@QR3 decimal(3,1), @QR4 decimal(3,1))
Returns decimal(3,1)
AS
Begin
Declare @temp Decimal(3,1)
Select @temp=Max(a.val)
From
(Select @QR1 Val
Union All
Select @QR2
Union All
Select @QR3
Union All
Select @QR4) a
Return @temp
End
GO
Create function dbo.SendMin (@QR1 decimal(3,1), @QR2 decimal(3,1),
@QR3 decimal(3,1), @QR4 decimal(3,1))
Returns decimal(3,1)
AS
Begin
Declare @temp Decimal(3,1)
Select @temp=Min(a.val)
From
(Select @QR1 Val
Union All
Select @QR2
Union All
Select @QR3
Union All
Select @QR4) a
Return @temp
End Go Select Supplier, dbo.SendMax(QR1,QR2,QR3,QR4) [MaxVal],
dbo.SendMin(QR1,QR2,QR3,QR4) [MinVal]
>From Temp1 Go Understand that Min and Max are table valued function and can be
>used in a column only. And I think you have realized the same. HTH, Vinod Kumar SCT
>Software Solutions.
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/BDotNet/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you received
this message by mistake, please click the "Remove" link below. On the pre-addressed
e-mail message that opens, simply click "Send". Your e-mail address will be deleted
from this group's mailing list.
mailto:[EMAIL PROTECTED]
