Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
srujanavinnakota
Starting Member
34 Posts |
 Posted - 2010-10-04 : 19:40:07
|
| Hi, Can anyone help me with the query. I have 4 date columns in below format.no Reference-Date Date1 Date2 Date3 1 2010-05-05 2010-05-09 2010-05-09 2010-05-06 2 2010-05-05 2010-05-06 2010-05-10 2010-05-15 3 2010-05-05 2010-05-08 2010-05-01 2010-05-10On Comparison of Reference-Date Column with Date1, Date2, Date3 columns. I need to get the closest Date. |
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2010-10-04 : 23:34:27
|
quote:
Originally posted by srujanavinnakota
Hi, Can anyone help me with the query. I have 4 date columns in below format.no Reference-Date Date1 Date2 Date3 1 2010-05-05 2010-05-09 2010-05-09 2010-05-06 2 2010-05-05 2010-05-06 2010-05-10 2010-05-15 3 2010-05-05 2010-05-08 2010-05-01 2010-05-10On Comparison of Reference-Date Column with Date1, Date2, Date3 columns. I need to get the closest Date.
By closest date you mean any date, either less than or equal to or greater than the reference date.If both less than and greater than have a tie, which one needs to be taken.Example:no Reference-Date Date1 Date2 Date3 1 2010-05-05 2010-05-03 2010-05-07 2010-05-08 |
 |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-10-05 : 04:27:17
|
| [code]declare @tbl as table(no int,ReferenceDate datetime, Date1 datetime, Date2 datetime, Date3 datetime )insert into @tblselect 1, '2010-05-05', '2010-05-09' ,'2010-05-09', '2010-05-06' union allselect 2, '2010-05-05', '2010-05-06' ,'2010-05-10', '2010-05-15' union allselect 3, '2010-05-05', '2010-05-08' ,'2010-05-01' ,'2010-05-10'select no, ReferenceDate, col Nearestdate from( select *,row_number()over(partition by no order by col)rid from ( select * from @tbl )u unpivot (col for columns in (date1,date2,date3))v )t where rid=1[/code]PBUH |
|
|
|
|
|
|
|
|
Get Nearest Date