April 9, 2012 at 2:25 pm
Lynn Pettis (4/9/2012)
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?
Here is my code here, but it returns blanks in columns:
ALTER PROCEDURE [dbo].[WEB_HR_GetRateHistory2]
(
@EMP_ID INT = 7
)
AS
BEGIN
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
)
DECLARE @x XML;
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.
END
April 9, 2012 at 2:28 pm
Okay, your procedure creates the temporary tables, but it never populates them.
April 9, 2012 at 2:30 pm
njdevils39 (4/9/2012)
Lynn Pettis (4/9/2012)
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?
Here is my code here, but it returns blanks in columns:
ALTER PROCEDURE [dbo].[WEB_HR_GetRateHistory2]
(
@EMP_ID INT = 7
)
AS
BEGIN
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
)
DECLARE @x XML;
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.
END
...as Sean goes over the bridge again he hears something faint...
Sean is pretty certain there is some heavy breathing sounds from under the bridge. Conjuring up the last of his patience and every ounce of courage he leans over and peers into the darkness. Staring back in his face is large gray face with two orange glowing embers. Upon close examination these embers appear to eyes...troll eyes to be precise. Suddenly the smoke starts pouring out of the nostrils of the troll when it is realized he has been discovered. Thanks to his quick wit Sean is able to scramble safely away from the abyss. Back on stable ground once again he motors off in search of other threads.
_______________________________________________________________
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/
April 9, 2012 at 2:35 pm
Sean Lange (4/9/2012)
...as Sean goes over the bridge again he hears something faint...Sean is pretty certain there is some heavy breathing sounds from under the bridge. Conjuring up the last of his patience and every ounce of courage he leans over and peers into the darkness. Staring back in his face is large gray face with two orange glowing embers. Upon close examination these embers appear to eyes...troll eyes to be precise. Suddenly the smoke starts pouring out of the nostrils of the troll when it is realized he has been discovered. Thanks to his quick wit Sean is able to scramble safely away from the abyss. Back on stable ground once again he motors off in search of other threads.
Party at the TitD!!!!
April 9, 2012 at 2:37 pm
April 27, 2012 at 1:27 pm
We have some major misunderstandings and we have reached many impasses on this topic, ok here is my DDL and Sample DATA. I have decided that we need a fresh start and I let the dust settle.
--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
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 here is my Stored Procedure code:
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 convert(varchar(10), 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 x.r.value('@rate','DECIMAL(10,5)') from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)) where x.r.value('@date_changed','DATETIME') = 'min(x.r.value('@date_changed','DATETIME'))',
TO_PAY_RATE = (select x.r.value('@rate','DECIMAL(10,5)') from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)) where x.r.value('@date_changed','DATETIME') = 'max(x.r.value('@date_changed','DATETIME'))'
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
Put emphasis on the last table in the DDL, I need one row return in my stored procedure which gives the to PAY_RATE values whether they are different or not. Note: Some XML Files have multiple rows.
April 27, 2012 at 1:41 pm
This looks excellent!!! I do have a question about your proc. It looks like you want to have this proc return data for 1 employee? If we used employee #3 (Whitehead) what do you want to output to be? What about #5? Just trying to understand exactly what you want returned but I think the code is fairly close at this point.
_______________________________________________________________
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/
April 27, 2012 at 1:52 pm
Sean Lange (4/27/2012)
This looks excellent!!! I do have a question about your proc. It looks like you want to have this proc return data for 1 employee? If we used employee #3 (Whitehead) what do you want to output to be? What about #5? Just trying to understand exactly what you want returned but I think the code is fairly close at this point.
Well the output will only be for one employee, all I have to input in the execute statement once the correct query or code is written is the organization_id and her/his EMP_ID.
But for Molly Whitehead the output that is in the DDL is as it is supposed to look, but the code in my stored proc is just a bit off in terms of getting that return for her and the next 3 employees I provided the DDL and Sample_Data for.
April 27, 2012 at 1:56 pm
njdevils39 (4/27/2012)
Sean Lange (4/27/2012)
This looks excellent!!! I do have a question about your proc. It looks like you want to have this proc return data for 1 employee? If we used employee #3 (Whitehead) what do you want to output to be? What about #5? Just trying to understand exactly what you want returned but I think the code is fairly close at this point.Well the output will only be for one employee, all I have to input in the execute statement once the correct query or code is written is the organization_id and her/his EMP_ID.
But for Molly Whitehead the output that is in the DDL is as it is supposed to look, but the code in my stored proc is just a bit off in terms of getting that return for her and the next 3 employees I provided the DDL and Sample_Data for.
OK I see what you mean about the DDL part since it is clearly defined in your proc. Let me try to ask this a different way. If you run this proc for Molly how many rows are you expecting and what should the data in the row(s) look like?
Your proc has two select statements at the end. Do you want the values from the temp table or the query before that one?
_______________________________________________________________
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/
April 27, 2012 at 2:39 pm
OK I realized your first select was filling your temp table and then you selected from it. Still not 100% what you want for output but here is a shot.
I removed the temp table. It really isn't needed since you fill it and then just immediately select everything from it.
See if this is close:
alter PROCEDURE [dbo].[WEB_HR_GetRateHistory]
(
@ORG_LEVEL_ID INT,
@EMP_ID INT
)
AS BEGIN
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 EMPLOYEE e
INNER JOIN EMPLOYEE_OTHER o ON e.EMP_ID=o.EMP_ID
CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)
) x where RowNum = 1 and EMP_ID = @EMP_ID
END
No clue what the @ORG_LEVEL_ID parameter means as far as the results.
_______________________________________________________________
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/
April 27, 2012 at 2:42 pm
Sean Lange (4/27/2012)
njdevils39 (4/27/2012)
Sean Lange (4/27/2012)
This looks excellent!!! I do have a question about your proc. It looks like you want to have this proc return data for 1 employee? If we used employee #3 (Whitehead) what do you want to output to be? What about #5? Just trying to understand exactly what you want returned but I think the code is fairly close at this point.Well the output will only be for one employee, all I have to input in the execute statement once the correct query or code is written is the organization_id and her/his EMP_ID.
But for Molly Whitehead the output that is in the DDL is as it is supposed to look, but the code in my stored proc is just a bit off in terms of getting that return for her and the next 3 employees I provided the DDL and Sample_Data for.
OK I see what you mean about the DDL part since it is clearly defined in your proc. Let me try to ask this a different way. If you run this proc for Molly how many rows are you expecting and what should the data in the row(s) look like?
Your proc has two select statements at the end. Do you want the values from the temp table or the query before that one?
For Molly, the data will be returned in the form of one row, the same way and same values as it is in the sample data in the DDL without the rowNum or Emp_ID. The last select statement in the stored proc is so I can get the data returned. (the select * from #Rate_History).
April 27, 2012 at 2:49 pm
Sean Lange (4/27/2012)
OK I realized your first select was filling your temp table and then you selected from it. Still not 100% what you want for output but here is a shot.I removed the temp table. It really isn't needed since you fill it and then just immediately select everything from it.
See if this is close:
alter PROCEDURE [dbo].[WEB_HR_GetRateHistory]
(
@ORG_LEVEL_ID INT,
@EMP_ID INT
)
AS BEGIN
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 EMPLOYEE e
INNER JOIN EMPLOYEE_OTHER o ON e.EMP_ID=o.EMP_ID
CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)
) x where RowNum = 1 and EMP_ID = @EMP_ID
END
No clue what the @ORG_LEVEL_ID parameter means as far as the results.
Sort of close, but I need the value of the FROM_PAY_RATE to be the value from the last row of the XML FILE.
April 27, 2012 at 2:54 pm
But let's continue with the original stored proc code I gave you:
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 convert(varchar(10), 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 x.r.value('@rate','DECIMAL(10,5)') from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)) where x.r.value('@date_changed','DATETIME') = 'min(x.r.value('@date_changed','DATETIME'))',
TO_PAY_RATE = (select x.r.value('@rate','DECIMAL(10,5)') from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)) where x.r.value('@date_changed','DATETIME') = 'max(x.r.value('@date_changed','DATETIME'))'
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
April 27, 2012 at 2:59 pm
OK so you want the most recent TO_PAY_RATE? I think we can do this.
The only thing I changed from your original was to remove the extra temp table. No need to fill a temp table, then just select the data and drop the temp table. It will perform better to just select the data and get out.
I am out of here for the weekend but we can pick this up Monday, unless somebody else comes along before then.
_______________________________________________________________
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/
April 27, 2012 at 3:02 pm
Sean Lange (4/27/2012)
OK so you want the most recent TO_PAY_RATE? I think we can do this.The only thing I changed from your original was to remove the extra temp table. No need to fill a temp table, then just select the data and drop the temp table. It will perform better to just select the data and get out.
I am out of here for the weekend but we can pick this up Monday, unless somebody else comes along before then.
Sure, no problem, but I will be out until Wednesday, but I will need the From_Pay_Rate to be the earliest date in the XML and the To_Pay_Rate will be linked to RowNum= 1 as usual.
Viewing 15 posts - 136 through 150 (of 194 total)
You must be logged in to reply to this topic. Login to reply