September 2, 2013 at 7:47 am
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...?
September 2, 2013 at 8:36 am
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
September 2, 2013 at 8:55 am
thats cool. Me too tried the same....
is there any other way ?
September 2, 2013 at 9:15 am
vignesh.ms (9/2/2013)
is there any other way ?
This way is fine. It just needs to be modified slightly to handle nulls.
[Code]
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
[/code]
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/
September 2, 2013 at 9:19 am
There might, depending on what the real information looks like. What's wrong with the given solution?
September 2, 2013 at 10:28 am
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
September 2, 2013 at 10:55 am
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/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply