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 02, 2013 7:47 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 8:27 AM
Points: 123, Visits: 326
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 02, 2013 8:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:10 PM
Points: 2,763, Visits: 5,909
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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

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

Group: General Forum Members
Last Login: Yesterday @ 8:27 AM
Points: 123, Visits: 326
thats cool. Me too tried the same....

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

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 17, 2014 10:37 PM
Points: 319, Visits: 1,142
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 02, 2013 9:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:10 PM
Points: 2,763, Visits: 5,909
There might, depending on what the real information looks like. What's wrong with the given solution?


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:10 PM
Points: 2,763, Visits: 5,909
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 17, 2014 10:37 PM
Points: 319, Visits: 1,142
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