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: Monday, July 6, 2015 1:28 AM
Points: 188, Visits: 516
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:25 AM
Points: 5,103, Visits: 11,685
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.
General Disclaimer:
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: Monday, July 6, 2015 1:28 AM
Points: 188, Visits: 516
thats cool. Me too tried the same....

is there any other way ?
Post #1490641
Posted Monday, September 2, 2013 9:15 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 10:53 PM
Points: 520, Visits: 2,366
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:25 AM
Points: 5,103, Visits: 11,685
There might, depending on what the real information looks like. What's wrong with the given solution?


Luis C.
General Disclaimer:
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:25 AM
Points: 5,103, Visits: 11,685
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.
General Disclaimer:
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
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 10:53 PM
Points: 520, Visits: 2,366
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