June 27, 2008 at 3:31 am
Ho,
I have two date fields in my table. I want to update a control table for each customer, using whichever date is later.
Can I achive this ? Sort of like this:
update tableb
set controldate = select MAX( date1 OR date2 *whichever later*) from tablea
where tableb.customer = tablea.customer
so I want the max date over the two columns. Hope I am making sense!!
Can anyone help please?
Thanks
June 27, 2008 at 3:44 am
You have to use CASE to achieve this.
Something like this
create table t1(c1 int Identity(1,1),
c2 datetime,
c3 datetime)
go
Insert into t1(c2,c3)
Select Getdate()-3, Getdate()-5
Union all
Select Getdate()-3, Getdate()-2
go
select c1,
Case
When c2 > c3 THEN c2
ELSE c3
END
As datecol
From t1
[font="Verdana"]Markus Bohse[/font]
June 27, 2008 at 3:45 am
Hi,
A CASE expression comparing the two date columns should do the trick. The pseudo code would look something like this...
update tableb b
set controldate =case (selectmax(date1)
fromtableb a1
wherea1.customer = a.customer)
>
(selectmax(date2)
fromtableb a2
wherea2.customer = b.customer)
when 1 then (selectmax(date1)
fromtableb a1
wherea1.customer = b.customer)
else(selectmax(date2)
fromtableb a2
wherea2.customer = a.customer)
end
from tablea a
where tableb.customer = tablea.customer
Good luck!
June 27, 2008 at 3:59 am
Brilliant! Many thanks to you both for your help!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply