Sean Lange (3/30/2012)

---

Like this?

select * from

(

SELECT

ROW_NUMBER() over(partition by e.EMP_ID order by x.r.value('@date_changed','DATETIME') desc) as RowNum,

e.EMP_ID,

e.EMP_NAME,

e.EMP_ORGANIZATION,

e.EMP_DEPARTMENT,

e.EMP_DT_TERMINATION,

...

Mark-101232 (3/30/2012)

---

Does this help?

SELECT e.EMP_ID,

e.EMP_NAME,

e.EMP_ORGANIZATION,

e.EMP_DEPARTMENT,

...

Here is the photo of the return data from my last statement.

OK, here are the EMP_ID's for all of these 4 employees.

/* SELECT * FROM EMPLOYEE WHERE EMP_NAME IN ('Whitehead, Molly','Gentry, Clinton A ','Crosby, Sherman','Riddle, Nichole') */

CREATE TABLE #EMPLOYEE2

(

EMP_ID INT,

EMP_NAME...

OK, here is my DDL and Sample Date, but yes the tables are not joining up. Again sorry for kindergarten type mistakes I may still be making. But this Sample...

capn.hector (3/26/2012)

---

njdevils39 (3/26/2012)

---

capn.hector (3/26/2012)

---

im amazed at your patience sean. im sitting here laughing. ill save you at least one time.

We need the DDL and sample data. im...

capn.hector (3/26/2012)

---

im amazed at your patience sean. im sitting here laughing. ill save you at least one time.

We need the DDL and sample data. im supprised you...

Lynn Pettis (3/26/2012)

---

Just to add, it doesn't even need to be the entire table(s). You can reduce the table(s) to the columns used in stored procedure and the key...

OK here is the XML FILE AND MY STORED PROCEDURE WHICH sort off works, just need to clean up the return values

<Rates>

<Rate date_changed="Nov 10 2011 7:28AM" not_active_since="07/27/2011"...

Ok here we go, here is the photo of the result set that I need, but the last three columns need to be other numbers from the XML files in...

Yes and I gave you an example of the How the Columns from the XML should be structured

Date CHanged = "mOst recent Date"

From Pay_Rate = 'initial rate from Earliest...

I am not having any trouble retrieving data from the employee table. Its the Rate_history column I am having trouble with in employee_other table.

The temp table is the join of two tables, the employee table and the employee_other table. There is no source table in this stored proc.

Its a simple join statement of...

This seems harder than the prototype I created, all I have to do is join the two tables, but only extract items from the rate_history XML column.

Give me a...

create table employee

(

EMP_NAME VARCHAR(255),

EMP_ORGANIZATION VARCHAR(255),

EMP_DEPARTMENT VARCHAR(255),

EMP_DT_TERMINATION DATETIME

)

create table employee_other

(

DATECHANGED DATETIME,

FROM_PAY_RATE INT,

TO_PAY_RATE INT,

CHANGED_BY VARCHAR(255)

)

insert into employee

SELECT emp_name,

emp_organization,

...