January 26, 2009 at 8:12 am
Hi All
I got a table which has 3 columns with datetime variable as below
Create table #Temp
( Nos int,
Date1 Datetime,
Date2 Datetime,
Date3 Datetime,
)
Insert into #temp (1,'2008-01-26 15:25:02.002','2008-01-23 15:25:02.002',null)
Insert into #temp (2,'2008-01-24 12:20:02.002','2008-01-21 15:25:02.002','2008-01-21 15:25:02.002')
Insert into #temp (3,'2008-01-21 15:08:02.002','2008-01-20 12:05:02.002',null)
Insert into #temp (4,'2008-01-22 11:45:02.002','2008-01-23 15:45:02.002',null)
i need a max of Date1, Date2,Date3 column for each row like
1, 2008-01-26 15:25:02.002
2 2008-01-24 12:20:02.002 etc
How can this be achived 🙂
Thanks for your post in advance
January 26, 2009 at 8:15 am
Check out the Case statement in Books Online.
You can write a pretty simple case statement to get this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 26, 2009 at 8:18 am
Thanks G-Squared you mean Case when date1 > date2 then Date1
when date2>date3 then Date2
Else Date3
End
Something like this, is there any other ways of doing this, just need to know the various ways for this, thanks once again for the instance response 🙂
January 26, 2009 at 8:28 am
select nos, max(date1)
from
(
select nos, date1 from #temp union all
select nos, date2 from #temp union all
select nos, date3 from #temp
)t
group by nos
January 26, 2009 at 8:36 am
If you use Case statements, include all necessary tests.
For this, you need to have something like:
case
when date1 > date2 and date1 > date3 then date1
when date2 > date1 and date2 > date3 then date2
else date3
end
The way you have it, if date1 is later than date2, but earlier than date3, it will still give you date1, because it doesn't check for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 26, 2009 at 8:38 am
Thanks Mate, is there any other ways to do it in 2005, or in 2008 🙂
January 26, 2009 at 8:48 am
You can use a case a mentioned before, but it is harder to code than the previous post shows, because you also have to allow for nulls if the columns are nullable. The way below is easier to code and test, especially when you have much more than 3 columns.
For a more complete discussion of this subject, with sample code and tests for both methods, look at this link:
MIN/MAX Across Multiple Columns
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906
select
Max_Date =
(
select
Max_Date = max(bb.xx)
from
(
select xx = a.Date1 where a.Date1 is not null union all
select xx = a.Date2 where a.Date2 is not null union all
select xx = a.Date3 where a.Date3 is not null
) bb
)
from
#Temp a
Edit: Here is the code for using a case statement showing the code needed with nullable columns:
select
Max_Date =
case
whena.Date1 is not null and
(a.Date1 >= a.Date2 or a.Date2 is null) and
(a.Date1 >= a.Date3 or a.Date3 is null)
then a.Date1
when a.Date2 is not null and
(a.Date2 >= a.Date1 or a.Date1 is null) and
(a.Date2 >= a.Date3 or a.Date3 is null)
then a.Date2
when a.Date3 is not null and
(a.Date3 >= a.Date1 or a.Date1 is null) and
(a.Date3 >= a.Date2 or a.Date2 is null)
then a.Date3
else null
end
from
#t a
January 26, 2009 at 8:49 am
ops forget to mention, the table contains around 4 million rows 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply