SqlServer 2008 date compare - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SqlServer 2008 date compare

Author

Topic

netsailer76
Starting Member

2 Posts

Posted - 2010-07-10 : 13:47:43

I have a table tblContract, In which there are three columns FlatID, FlatNumber ContractToDate the data goes as below.
FlatId FlatNumber ContractToDate
1 FLT1 01/01/2008
2 FLT2 01/01/2008
3 FLT3 01/01/2008
4 FLT1 01/01/2009
5 FLT2 01/01/2009
6 FLT3 01/01/2009
7 FLT1 01/01/2010
8 FLT2 01/01/2010
9 FLT3 01/01/2010

Now what i need is.. while i search for FLT1, i need FlatId which has the recent ContractToDate. Kindly note, FLT1 is there in ContractToDates, 2008,2009,2010 but with different FlatIds 1,4,9. The query should return 9 as result since it is the recent ContractToDate. Any help would be much appreciated.
Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-07-10 : 18:58:16

SELECT FlatId
FROM
tblContract t1
INNER JOIN
(select max(ContractToDate) as ContractToDate
from tblContract
where FlatNumber = 'FLT1'
) t2
ON
t1.ContractToDate = t2.ContractToDate
and t1.FlatNatNumber = 'FLT1'

Jim

Everyday I learn something that somebody else already knew

netsailer76
Starting Member

2 Posts

Posted - 2010-07-10 : 23:33:47

works super thanks man. you are my STAR of the day

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-11 : 01:46:52

this should also do:


select top 1 * 
	from tblContract 
	where FlatNumber = 'FLT1' 
order by ContractToDate desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources