Compare datetimes from two tables - select latest

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 2005 Forums

Transact-SQL (2005)

Compare datetimes from two tables - select latest

Author

Topic

rlull
Starting Member

39 Posts

Posted - 2011-03-08 : 19:48:45

I need to compare dates from two tables, select the latest one and insert it into a column in the first table.

For example:

select a.firstdate, b.seconddate
from firstTable a
join secondTable b
on a.foreignKey = b.foreignKey

//pseudocode here
compare a.firstDate to b.secondDate
if a.firstDate > b.secondDate
insert a.firstDate into firstTable column X
if b.secondDate > a.firstDate
insert b.firstDate into firstTable column X

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-08 : 21:04:40

[code]insert into firstTable (columnX)
select
case
when a.firstdate > b.seconddate then a.firstdate
when b.seconddate > a.firstdate then b.firstdate
end
from
firstTable a
join secondTable b on a.foreignkey = b.foreignkey[/code]You may want to consider what should happen when the two dates are equal.

This would insert 0 or more new rows into the firstTable. Is that you want to happen, or is the goal to update a column for each record? If it is to update, don't use this code.

rlull
Starting Member

39 Posts

Posted - 2011-03-09 : 09:55:56

Thanks sunitabeck - but yes, I need to update columnX in the firstTable not insert a new row. If the two dates are equal, I will use a.firstdate.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-09 : 11:41:12

[code]update a set
columnX =
-- insert into firstTable (columnX)
-- select
case
when a.firstdate >= b.seconddate then a.firstdate
when b.seconddate > a.firstdate then b.firstdate
end
from
firstTable a
join secondTable b on a.foreignkey = b.foreignkey[/code]

rlull
Starting Member

39 Posts

Posted - 2011-03-09 : 12:48:28

Excellent, thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources