Hey There,

Pretty nifty query. It actually works if you change the following:

group by

tT.employeeid,

tT.TranDte

having

max(ah.updatedate) <= TranDte

)

If you run the...

OH. Got Ya.

Yes, I am using case insensitive collation.

Sorry,

AL

Here is everything that I have on my query session:

create table address_history

(

employeeid int,

updatedate datetime,

address1 varchar(100),

address2 varchar(100),

city varchar(50),

state char(2),

zip varchar(15)

)

insert into address_history

select 1, '7/13/2008', '123 NW 3rd Ave','','New York', 'NY', '11001'

union...

Could you explain? Please.

I don't follow.

AL

The output returned is correct:

select t.employeeid, t.TranDte,

dbo.udf_getHistory(t.TranDte, t.employeeid, 1) as Address1,

dbo.udf_getHistory(t.TranDte, t.employeeId, 2) as Address2,

dbo.udf_getHistory(t.TranDte, t.employeeId, 3) as City,

dbo.udf_getHistory(t.TranDte, t.employeeId, 4) as...

How I am going to code the select statement adding the logic from the udf and get the correct history row with the fields?

Could you rough it up for me?...

OK. Here is the code.

Give it a try..

Thanks,

AL

create table address_history

(

employeeid int,

updatedate datetime,

address1 varchar(100),

address2 varchar(100),

city varchar(50),

state char(2),

zip varchar(15)

)

insert into address_history

select 1, '7/13/2008', '123 NW 3rd Ave','','New York', 'NY', '11001'

union all

select 1,...

I will post in a few minutes...

Thanks,

AL

Hey thanks for the quick reply,

I would use a direct join. However, there are not enough records on a 1 to 1 relationship between the 2 tables. The history table...

Chris,

Thanks for your response.

I figured I would have to play around with the arguable side to leave the field alone.

Thanks again,

AL

Gail,

Thanks for your insight into the configuration of SQL.

AL

What would your opinion be on the following configuration:

* Windows 2003 Server OS SP3 - Pagefile in C:

* Intel Xeon 3.8ghz processor with hiperthread

* 4 gigs of memory

* Raid 5...

Chris,

I couldn't bare the pain any longer reading your suggestions.

Let me give hime the answer:

SELECT T1.ObjectID FROM....

SELECT T2.ObjectID FROM ....

see below:

IF EXISTS(SELECT T1.ObjectID FROM @tmp_subjects t1

...

If you have the disk space, I would just re-create the table with the updated values then drop the TRANS table and then rename NEW_TRANS to TRANS again. ie:

select into...