July 11, 2008 at 12:40 pm
Hi ,
I was wondering if you could help me.
Basically , the query below works but it selects any random row less then [current effective date].
update trend_analysis
set [past effective date 1] = s.effective_date , [Past Rate 1] = s.rate
from trend_analysis t , tempratehistory s
where s.effective_date < [Current Effective Date] and s.country_type = country and s.carrier = t.carrier
I want it to select the Top row from tempratehistory.
I tried putting having max(s.effective_date) at the end , it didnt work
Please,You will get my gratitude for your kind help!
THANK YOU!!
July 11, 2008 at 1:54 pm
You need to set it to get a subquery to get the aggregate.
So
create table MyTable
( myID int
,salesdate datetime
, myval int
)
go
create table NewTable
( myID int
, newdate datetime
, myval int
)
go
insert MyTable select 1, '1/1/08', 5
insert MyTable select 1, '2/1/08', 10
insert MyTable select 1, '3/1/08', 15
insert MyTable select 2, '1/1/08', 3
insert MyTable select 2, '2/1/08', 6
insert MyTable select 2, '3/1/08', 7
insert MyTable select 2, '4/1/08', 9
insert NewTable select 1, '1/1/08', 1
insert NewTable select 2, '1/1/08', 1
go
select * from Mytable
select * from Newtable
go
update a
set NewDate = b.SalesDate, myval = b.myval
from NewTable a
inner join MyTable b
on a.MyID = b.MyID
and b.SalesDate =
( select max( salesdate) 'salesdate'
from MyTable c
where c.MyID = b.MyID
)
go
drop table MyTable
drop table NewTable
July 11, 2008 at 3:14 pm
You need to use TOP with an ORDER BY clause to give it what BOL calls "a meaningful chronology":
update TOP (1) s
set [past effective date 1] = s.effective_date
, [Past Rate 1] = s.rate
from tempratehistory s
Join trend_analysis t ON s.carrier = t.carrier
where s.effective_date < [Current Effective Date]
and s.country_type = country
Order By s.effective_date DESC
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 12, 2008 at 2:46 pm
Thanks for replying.
I tried the query below
update TOP (1) s
set [past effective date 1] = s.effective_date , [Past Rate 1] = s.rate
from tempratehistory s Join trend_analysis t ON s.carrier = t.carrier
where s.effective_date < [Current Effective Date]and s.country_type = country
order by s.effective_date
and it gives me incorrect syntax next to 'order'
if i comment out the last line, it gives me
invalid column, past effective date 1 and past rate 1
I think its because its trying to update S (tempratehistory) instead of T(trend_analysis).
So is a subquery , the only way??
July 12, 2008 at 3:44 pm
Hi Guys,
I tried this query and it works:
update a
set [past effective date 1] = b.effective_date , [Past Rate 1] = b.rate
from trend_analysis a inner join tempratehistory b
on a.country = b.country_type and a.carrier = b.carrier and b.effective_date =
( select max( effective_date)
from tempratehistory c
where c.country_type = b.country_type and c.carrier = b.carrier
) where b.effective_date < [Current Effective Date]
I added the last line to Steve Jones query and I think it seems to do the trick!;)
Thanks alot to rbarryyoung and Steve Jones!!:D
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply