How to convert this XML into columns
-
njdevils39 (4/9/2012)
By the way, I also do not work yet or get paid. I am an unpaid intern learning to advance my programming knowledge.And those of us trying to help you don't get paid to help you. We are volunteers on this site and help on our own time. That's why we ask for as much information as possible and in a format that we can readily use without editting it.
-
Lynn Pettis (4/9/2012)
njdevils39 (4/9/2012)
By the way, I also do not work yet or get paid. I am an unpaid intern learning to advance my programming knowledge.And those of us trying to help you don't get paid to help you. We are volunteers on this site and help on our own time. That's why we ask for as much information as possible and in a format that we can readily use without editting it.
Well Sean asked me for whatever I have so far and hopefully he can help despite my nonsense I am giving him.
-
njdevils39 (4/9/2012)
By the way, I also do not work yet or get paid. I am an unpaid intern learning to advance my programming knowledge.You're receiving high-quality, free assistance and advice and now you're whining. If you'd like to change your 'unpaid' status, you might like to take some of it onboard with good grace.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Can you put together what you want as desired output? Hard code into a table or whatever. That way I have an idea that what I get is matching what you are expecting?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean Lange (4/9/2012)
Can you put together what you want as desired output? Hard code into a table or whatever. That way I have an idea that what I get is matching what you are expecting?OK here is the image of the result set of the Sample Data and DDL:
Now here is my Sample Date and DDL once again:
-- SELECT * FROM EMPLOYEE WHERE EMP_NAME IN ('Whitehead, Molly','Gentry, Clinton A ','Crosby, Sherman','Riddle, Nichole')
CREATE TABLE #EMPLOYEE2
(
EMP_ID INT,
EMP_NAME VARCHAR(MAX),
EMP_ORGANIZATION VARCHAR(MAX),
EMP_DEPARTMENT VARCHAR(MAX),
EMP_DT_TERMINATION DATETIME
)
CREATE TABLE #EMPLOYEE_OTHER2
(
EMP_ID INT,
RATE_HISTORY XML
)
INSERT INTO #EMPLOYEE2
(
EMP_ID,
EMP_NAME,
EMP_ORGANIZATION,
EMP_DEPARTMENT,
EMP_DT_TERMINATION
)
(SELECT '3' , 'Whitehead, Molly ', 'Silver Manor', 'Administrative', NULL UNION ALL
SELECT '5' , 'Gentry, Clinton A ', 'Silver Manor', 'Administrative', NULL UNION ALL
SELECT '6' , 'Crosby, Sherman', 'Silver Manor', 'Administrative', NULL UNION ALL
SELECT '7' , 'Riddle, Nichole', 'Silver Manor', 'Maintenance', NULL
)
INSERT INTO #EMPLOYEE_OTHER2
(
EMP_ID,
RATE_HISTORY
)
(SELECT '3' , '<Rates>
<Rate date_changed="Mar 6 2011 12:00AM" not_active_since="03/06/2011" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Jan 25 2009 12:00AM" not_active_since="01/25/2009" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="3461.54" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Sep 21 2008 12:00AM" not_active_since="09/21/2008" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>'
UNION ALL
SELECT '5' ,
'<Rates>
<Rate date_changed="Apr 5 2009 12:00AM" not_active_since="04/05/2009" rate="11.73" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="11.44" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>'
UNION ALL
SELECT '6' ,
'<Rates>
<Rate date_changed="Feb 22 2009 12:00AM" not_active_since="02/22/2009" rate="17.43" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>'
UNION ALL
SELECT '7' ,
'<Rates>
<Rate date_changed="Feb 8 2009 12:00AM" not_active_since="02/08/2009" rate="14.00" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>'
)
SELECT * FROM #EMPLOYEE2
select * from #EMPLOYEE_OTHER2
DECLARE @x XML;
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,
convert(VARCHAR(10), x.r.value('@date_changed','DATETIME'), 101) AS DATE_CHANGED,
x.r.value('@rate','DECIMAL(10,5)') AS FROM_PAY_RATE,
x.r.value('@rate','DECIMAL(10,5)') AS TO_PAY_RATE
FROM #EMPLOYEE2 e
INNER JOIN #EMPLOYEE_OTHER2 o ON e.EMP_ID=o.EMP_ID
CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)
) x where RowNum = 1
The image above is the resultset for this code.
Now here are the XML FILES in order of those 4 employees:
<Rates>
<Rate date_changed="Mar 6 2011 12:00AM" not_active_since="03/06/2011" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Jan 25 2009 12:00AM" not_active_since="01/25/2009" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="3461.54" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Sep 21 2008 12:00AM" not_active_since="09/21/2008" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>
For this one I need FROM_PAY_RATE to = 1730.77 and obviously it will match the TO_PAY_RATE column which is fine. This is for Molly Whitehead.
<Rates>
<Rate date_changed="Apr 5 2009 12:00AM" not_active_since="04/05/2009" rate="11.73" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="11.44" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>
This is for Gentry, Clinton A: FROM_PAY_RATE = 11.44 and TO_PAY_RATE = 11.73
<Rates>
<Rate date_changed="Feb 22 2009 12:00AM" not_active_since="02/22/2009" rate="17.43" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>
This is for Crosby, Sherman. Obviously both columns will match.
<Rates>
<Rate date_changed="Feb 8 2009 12:00AM" not_active_since="02/08/2009" rate="14.00" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>
This is for Riddle, Nichole, where both columns will match. But In every result even in my stored procedure, every column is returning the result I want it to return except for the FROM_PAY_RATE COLUMN.
-
Well here is the challenge...for some reason our "net nanny" does not allow images from ssc. Maybe somebody else that can view the image can help? Otherwise can you post this as text or even a series of inserts into some other temp table? Gotta love the net nanny.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
You still havent learned that we'd like to know your EXPECTED result. NOT the ACTUAL result which we all know is not what you're looking for.
PLEASE, PLEASE, PLEASE tell us what your result set SHOULD look like.
And also, if there are any duplicate values (like for empid = 3), please tell us what value should be there (not the number itself, we've seen it a dozend of times), but the LOGIC BEHIND.
If you go a few posts back, I posted a solution with a self reference to the xml data in order to get the refernce to the previous row. This might help you to solve the problem for this gentlemen...
The OP seems to ignore my solution so you might just repost it and become his/her heroe...
-
Sean Lange (4/9/2012)
Well here is the challenge...for some reason our "net nanny" does not allow images from ssc. Maybe somebody else that can view the image can help? Otherwise can you post this as text or even a series of inserts into some other temp table? Gotta love the net nanny.Give me your email if you are allowed and I will send you the picture,
but in the code I inserted something 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,
convert(VARCHAR(10), x.r.value('@date_changed','DATETIME'), 101) AS DATE_CHANGED,
11.44 AS FROM_PAY_RATE,
x.r.value('@rate','DECIMAL(10,5)') AS TO_PAY_RATE
FROM #EMPLOYEE2 e
INNER JOIN #EMPLOYEE_OTHER2 o ON e.EMP_ID=o.EMP_ID
CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)
) x where RowNum = 1
This would only be correct for Clinton Gentry.
-
Not really too hard. Just hard code something like this but with the values you expect.
select 1 as RowNum, 3 as EMP_ID, 'Whitehead, Molly' as EMP_NAME, 'Silver Manor' as EMP_ORGANIZATION, 'Administrative' as EMP_DEPARTMENT, NULL as EMP_DT_TERMINATION, '03/06/2011' as DATE_CHANGED, '1730.77000' as FROM_PAY_RATE, '1730.77000' as TO_PAY_RATE
union all select 1, 5, 'Gentry, Clinton A', 'Silver Manor', 'Administrative', NULL, '04/05/2009', '11.73000', '11.73000'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
njdevils39 (4/9/2012)
Sean Lange (4/9/2012)
Well here is the challenge...for some reason our "net nanny" does not allow images from ssc. Maybe somebody else that can view the image can help? Otherwise can you post this as text or even a series of inserts into some other temp table? Gotta love the net nanny.Give me your email if you are allowed and I will send you the picture,
but in the code I inserted something 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,
convert(VARCHAR(10), x.r.value('@date_changed','DATETIME'), 101) AS DATE_CHANGED,
11.44 AS FROM_PAY_RATE,
x.r.value('@rate','DECIMAL(10,5)') AS TO_PAY_RATE
FROM #EMPLOYEE2 e
INNER JOIN #EMPLOYEE_OTHER2 o ON e.EMP_ID=o.EMP_ID
CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)
) x where RowNum = 1
This would only be correct for Clinton Gentry.
It would be easier if you created a table (say, ExpectedResults), provide a series of INSERT INTO statements that populates this table with the EXPECTED results based on the sample data.
This would give us something to test our results against to see if we are meeting your requirements.
-
Here's the link to the solution I posted.
The OP's answer?
Neither "This will work." nor "The logic should be..."
But instead: "Where should I put this query in my original SQL CODE?"
-
Sean Lange (4/9/2012)
Not really too hard. Just hard code something like this but with the values you expect.select 1 as RowNum, 3 as EMP_ID, 'Whitehead, Molly' as EMP_NAME, 'Silver Manor' as EMP_ORGANIZATION, 'Administrative' as EMP_DEPARTMENT, NULL as EMP_DT_TERMINATION, '03/06/2011' as DATE_CHANGED, '1730.77000' as FROM_PAY_RATE, '1730.77000' as TO_PAY_RATE
union all select 1, 5, 'Gentry, Clinton A', 'Silver Manor', 'Administrative', NULL, '04/05/2009', '11.73000', '11.73000'
Ok here is the result set that I want for my Sample Data and DDL:
Here is the Sample Data and DDL:
-- SELECT * FROM EMPLOYEE WHERE EMP_NAME IN ('Whitehead, Molly','Gentry, Clinton A ','Crosby, Sherman','Riddle, Nichole')
CREATE TABLE #EMPLOYEE2
(
EMP_ID INT,
EMP_NAME VARCHAR(MAX),
EMP_ORGANIZATION VARCHAR(MAX),
EMP_DEPARTMENT VARCHAR(MAX),
EMP_DT_TERMINATION DATETIME
)
CREATE TABLE #EMPLOYEE_OTHER2
(
EMP_ID INT,
RATE_HISTORY XML
)
INSERT INTO #EMPLOYEE2
(
EMP_ID,
EMP_NAME,
EMP_ORGANIZATION,
EMP_DEPARTMENT,
EMP_DT_TERMINATION
)
(SELECT '3' , 'Whitehead, Molly ', 'Silver Manor', 'Administrative', NULL UNION ALL
SELECT '5' , 'Gentry, Clinton A ', 'Silver Manor', 'Administrative', NULL UNION ALL
SELECT '6' , 'Crosby, Sherman', 'Silver Manor', 'Administrative', NULL UNION ALL
SELECT '7' , 'Riddle, Nichole', 'Silver Manor', 'Maintenance', NULL
)
INSERT INTO #EMPLOYEE_OTHER2
(
EMP_ID,
RATE_HISTORY
)
(SELECT '3' , '<Rates>
<Rate date_changed="Mar 6 2011 12:00AM" not_active_since="03/06/2011" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Jan 25 2009 12:00AM" not_active_since="01/25/2009" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="3461.54" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Sep 21 2008 12:00AM" not_active_since="09/21/2008" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>'
UNION ALL
SELECT '5' ,
'<Rates>
<Rate date_changed="Apr 5 2009 12:00AM" not_active_since="04/05/2009" rate="11.73" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="11.44" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>'
UNION ALL
SELECT '6' ,
'<Rates>
<Rate date_changed="Feb 22 2009 12:00AM" not_active_since="02/22/2009" rate="17.43" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>'
UNION ALL
SELECT '7' ,
'<Rates>
<Rate date_changed="Feb 8 2009 12:00AM" not_active_since="02/08/2009" rate="14.00" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>'
)
SELECT * FROM #EMPLOYEE2
select * from #EMPLOYEE_OTHER2
DECLARE @x XML;
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,
convert(VARCHAR(10), x.r.value('@date_changed','DATETIME'), 101) AS DATE_CHANGED,
x.r.value('@rate','DECIMAL(10,5)') AS FROM_PAY_RATE,
x.r.value('@rate','DECIMAL(10,5)') AS TO_PAY_RATE
FROM #EMPLOYEE2 e
INNER JOIN #EMPLOYEE_OTHER2 o ON e.EMP_ID=o.EMP_ID
CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)
) x where RowNum = 1
Here is my sample result set hardcode:
select 1 as RowNum, 3 as EMP_ID, 'Whitehead, Molly' as EMP_NAME, 'Silver Manor' as EMP_ORGANIZATION, 'Administrative' as EMP_DEPARTMENT, NULL as EMP_DT_TERMINATION, '03/06/2011' as DATE_CHANGED, '1730.77' as FROM_PAY_RATE, '1730.77' as TO_PAY_RATE
union all
select 1, 5, 'Gentry, Clinton A', 'Silver Manor', 'Administrative', NULL, '04/05/2009', '11.44', '11.73'
union all
select 1, 6, 'Crosby, Sherman','Silver Manor','Administrative', NULL, '02/22/2009','17.43', '17.43'
union all
select 1, 7, 'Riddle, Nichole','Silver Manor','Maintenance', NULL, 'Maintenance','14.00', '14.00'
Now its just about getting it into this stored procedure:
ALTER PROCEDURE [dbo].[WEB_HR_GetRateHistory]
(
@ORG_LEVEL_ID INT,
@EMP_ID INT
)
AS
BEGIN
DECLARE @x XML;
CREATE TABLE #RATE_HISTORY
(
EMP_NAME VARCHAR(255),
EMP_ORGANIZATION VARCHAR(255),
EMP_DEPARTMENT VARCHAR(255),
EMP_DT_TERMINATION DATETIME,
DATE_CHANGED DATETIME,
FROM_PAY_RATE NVARCHAR(MAX),
TO_PAY_RATE NVARCHAR(MAX)
)
INSERT INTO #RATE_HISTORY
(
EMP_NAME,
EMP_ORGANIZATION,
EMP_DEPARTMENT,
EMP_DT_TERMINATION,
DATE_CHANGED,
FROM_PAY_RATE,
TO_PAY_RATE
)
SELECT
e.emp_name,
e.emp_organization,
e.emp_department,
e.emp_dt_termination,
DATE_CHANGED = (select max(convert(varchar(max), x.r.value('@date_changed','DATETIME'), 101) from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)),
FROM_PAY_RATE = (select min(x.r.value('@rate','DECIMAL(10,5)')) from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)),
TO_PAY_RATE = (select max(x.r.value('@rate','DECIMAL(10,5)')) from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r))
FROM employee e
JOIN employee_other eo ON e.emp_id = eo.emp_id
where e.emp_id = @emp_id and (emp_dt_termination is NULL) and e.emp_organization_id = @ORG_LEVEL_ID
select * from #RATE_HISTORY
END
-
Wow here we have another last minute addition to the specs...sheesh.
The code Lutz posted about 20-30 posts ago was more than 99% of what you needed.
declare @emp_id int = 7
;WITH cteRates AS
(
SELECT
o.EMP_ID,
(ROW_NUMBER() over(partition by o.EMP_ID order by x.r.value('@date_changed','DATETIME') desc)) as RowNum,
convert(VARCHAR(10), x.r.value('@date_changed','DATETIME'), 101) AS DATE_CHANGED,
x.r.value('@rate','DECIMAL(10,5)') AS FROM_PAY_RATE
FROM #EMPLOYEE_OTHER2 o
CROSS APPLY o.rate_history.nodes('/Rates/Rate') AS x(r)
)
SELECT
e.*,
c1.Emp_id,
c1.DATE_CHANGED,
isnull(c2.FROM_PAY_RATE, c1.FROM_PAY_RATE) AS fromRate,
c1.FROM_PAY_RATE AS ToRate
FROM #EMPLOYEE2 e
INNER JOIN cteRates c1 ON e.EMP_ID=c1.EMP_ID
left JOIN cteRates c2 ON c1.EMP_ID=c2.EMP_ID AND c1.RowNum=1 AND c2.RowNum=2
--added the where clause for yet another last minute addition to the requirements.
where e.EMP_DT_TERMINATION is null
and e.EMP_ID = @emp_id
--and ?? = @ORG_LEVEL_ID --this is a new field that is not in the ddl you provided.
Is this close to what you are after?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean Lange (4/9/2012)
Wow here we have another last minute addition to the specs...sheesh.The code Lutz posted about 20-30 posts ago was more than 99% of what you needed.
declare @emp_id int = 7
;WITH cteRates AS
(
SELECT
o.EMP_ID,
(ROW_NUMBER() over(partition by o.EMP_ID order by x.r.value('@date_changed','DATETIME') desc)) as RowNum,
convert(VARCHAR(10), x.r.value('@date_changed','DATETIME'), 101) AS DATE_CHANGED,
x.r.value('@rate','DECIMAL(10,5)') AS FROM_PAY_RATE
FROM #EMPLOYEE_OTHER2 o
CROSS APPLY o.rate_history.nodes('/Rates/Rate') AS x(r)
)
SELECT
e.*,
c1.Emp_id,
c1.DATE_CHANGED,
isnull(c2.FROM_PAY_RATE, c1.FROM_PAY_RATE) AS fromRate,
c1.FROM_PAY_RATE AS ToRate
FROM #EMPLOYEE2 e
INNER JOIN cteRates c1 ON e.EMP_ID=c1.EMP_ID
left JOIN cteRates c2 ON c1.EMP_ID=c2.EMP_ID AND c1.RowNum=1 AND c2.RowNum=2
--added the where clause for yet another last minute addition to the requirements.
where e.EMP_DT_TERMINATION is null
and e.EMP_ID = @emp_id
--and ?? = @ORG_LEVEL_ID --this is a new field that is not in the ddl you provided.
Is this close to what you are after?
@EMP_ID and @ORG_LEVEL_ID are input parameters.
-
njdevils39 (4/9/2012)
Sean Lange (4/9/2012)
Wow here we have another last minute addition to the specs...sheesh.The code Lutz posted about 20-30 posts ago was more than 99% of what you needed.
declare @emp_id int = 7
;WITH cteRates AS
(
SELECT
o.EMP_ID,
(ROW_NUMBER() over(partition by o.EMP_ID order by x.r.value('@date_changed','DATETIME') desc)) as RowNum,
convert(VARCHAR(10), x.r.value('@date_changed','DATETIME'), 101) AS DATE_CHANGED,
x.r.value('@rate','DECIMAL(10,5)') AS FROM_PAY_RATE
FROM #EMPLOYEE_OTHER2 o
CROSS APPLY o.rate_history.nodes('/Rates/Rate') AS x(r)
)
SELECT
e.*,
c1.Emp_id,
c1.DATE_CHANGED,
isnull(c2.FROM_PAY_RATE, c1.FROM_PAY_RATE) AS fromRate,
c1.FROM_PAY_RATE AS ToRate
FROM #EMPLOYEE2 e
INNER JOIN cteRates c1 ON e.EMP_ID=c1.EMP_ID
left JOIN cteRates c2 ON c1.EMP_ID=c2.EMP_ID AND c1.RowNum=1 AND c2.RowNum=2
--added the where clause for yet another last minute addition to the requirements.
where e.EMP_DT_TERMINATION is null
and e.EMP_ID = @emp_id
--and ?? = @ORG_LEVEL_ID --this is a new field that is not in the ddl you provided.
Is this close to what you are after?
@EMP_ID and @ORG_LEVEL_ID are input parameters.
Here is the CREATE TABLE statement for #EMPLOYEE2:
CREATE TABLE #EMPLOYEE2
(
EMP_ID INT,
EMP_NAME VARCHAR(MAX),
EMP_ORGANIZATION VARCHAR(MAX),
EMP_DEPARTMENT VARCHAR(MAX),
EMP_DT_TERMINATION DATETIME
)
Do you see a column named emp_organization_id any where in the definition of the table?
Viewing 15 posts - 121 through 135 (of 194 total)
You must be logged in to reply to this topic. Login to reply