Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need Help on query Expand / Collapse
Author
Message
Posted Monday, September 2, 2013 7:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 141, Visits: 398
create table sample ( a date,b date, c date ,d date )

insert into sample values (null,GETDATE(),GETDATE(),GETDATE())
insert into sample values (null,GETDATE(),GETDATE(),null)
insert into sample values (GETDATE(),GETDATE(),null,GETDATE())
insert into sample values (GETDATE(),GETDATE()+1,GETDATE(),null)

select * from sample


I need following output

select a , b, c , max(a,b,c) sample

can any 1 help...?
Post #1490628
Posted Monday, September 2, 2013 8:36 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:15 PM
Points: 3,613, Visits: 8,104
Thank you for posting your sample data. If I understand correctly, the following query should help you.

SELECT a , 
b,
c ,
CASE WHEN a >= b AND a >= c THEN a
WHEN b >= c THEN b
ELSE c END
FROM sample




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1490637
Posted Monday, September 2, 2013 8:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 141, Visits: 398
thats cool. Me too tried the same....

is there any other way ?
Post #1490641
Posted Monday, September 2, 2013 9:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:05 PM
Points: 355, Visits: 1,469
vignesh.ms (9/2/2013)

is there any other way ?


This way is fine. It just needs to be modified slightly to handle nulls.

Select a, b, c, 
case when isNull(a, getDate()-1000) >= isNull(b, getDate()-1000) and isNull(a, getDate()-1000) >= isNull(c, getDate()-1000) then a
when isNull(b, getDate()-1000) >= isNull(c, getDate()-1000) then b
else c end
From sample

Note: I pulled getDate()-1000 out of thin air. It just needs to be lower than any other date in your system. 0 does not work nor does casting it to date work. I am sure there is a more elegant way to check for a null date in this context, but this works.



__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1490649
Posted Monday, September 2, 2013 9:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:15 PM
Points: 3,613, Visits: 8,104
There might, depending on what the real information looks like. What's wrong with the given solution?


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1490650
Posted Monday, September 2, 2013 10:28 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:15 PM
Points: 3,613, Visits: 8,104
LinksUp (9/2/2013)
vignesh.ms (9/2/2013)

is there any other way ?


This way is fine. It just needs to be modified slightly to handle nulls.



Why would you need to handle nulls? Null won't be greater than anything.
I reviewed the results ans checked the need for a null validation, however it's not needed on all columns and date zero is possible.
SELECT a, b, c, CASE WHEN a >= ISNULL(b, CAST( 0 AS DATETIME)) AND a >= ISNULL(c, CAST( 0 AS DATETIME)) THEN a 
WHEN b >= c THEN b
ELSE c END
FROM sample




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1490662
Posted Monday, September 2, 2013 10:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:05 PM
Points: 355, Visits: 1,469
Luis Cazares (9/2/2013)

I reviewed the results ans checked the need for a null validation, however it's not needed on all columns and date zero is possible.


You are right. I was trying to cast to a Date, which did not work, but DateTime works perfectly!

You will also need to do an isNull check on the 2nd When of the case statement. You can verify this by using the expanded data set below:

declare @t1 table
(
a date
,b date
,c date
,d date
)

insert @t1 (a,b,c,d) values
(null, getdate(), getdate()+1, getdate()+2),
(getdate()+2, null, getdate(), null),
(getdate()+3, getdate()+1, null, getdate()),
(getdate(), getdate()+2, getdate(), null),
(GETDATE(), null, null, null),
(null, GETDATE(), null, null),
(null, null, getdate(), null),
(null, null, null, null)


SELECT a, b, c, CASE WHEN a >= ISNULL(b, CAST( 0 AS DATETIME)) AND a >= ISNULL(c, CAST( 0 AS DATETIME)) THEN a
WHEN b >= isnull(c, CAST(0 as DATETIME)) THEN b
ELSE c END MaxDate
FROM @t1



__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1490665
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse