SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need Help on query


Need Help on query

Author
Message
vignesh.ms
vignesh.ms
SSC Eights!
SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)

Group: General Forum Members
Points: 848 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...?
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43358 Visits: 19859
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
vignesh.ms
vignesh.ms
SSC Eights!
SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)

Group: General Forum Members
Points: 848 Visits: 516
thats cool. Me too tried the same....

is there any other way ?
LinksUp
LinksUp
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1773 Visits: 4640
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/
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43358 Visits: 19859
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43358 Visits: 19859
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
LinksUp
LinksUp
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1773 Visits: 4640
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search