May 2, 2012 at 10:46 am
Sean Lange (5/2/2012)
patrickmcginnis59 (5/2/2012)
Sean Lange (5/2/2012)
Well unless you are doing on the most god awful things I have seen some sql people do...which is to create a temp table and then call a stored proc which depends on it being run within the same connection. That will work but it is an absolutely hideous idea. This is one of the rare times I am totally confident in using an absolute in sql.NEVER NEVER NEVER use temp tables inside of a proc that were declared elsewhere.
This seems like something you are fairly certain about. Is there a reason that this isn't a good idea?
Keep reading the follow up. It is more from personal experience of this kind of thing running haywire than anything else.
Well I read your follow up, but I was looking more for some discussion on this practice itself. I can see some negatives myself but I can see them being handy when declared in one routine, then used in another routine that is called from the first routine that declared it, especially when using routines (separate procedures) to break up a process into more manageable pieces.
May 2, 2012 at 10:50 am
It can get really difficult to test this fairly easily. It is difficult to test the second proc. You have to go back and figure out how to create the temp table and populate it along with any other data modification that has been made to it up to that point. Imagine trying to accomplish this in the scenario I described. Now think about debugging the proc that is 4 procs deep after creating the temp table. I would rather take out my eyeballs and peel them than deal with that type of nightmare again.
_______________________________________________________________
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/
May 2, 2012 at 10:50 am
patrickmcginnis59 (5/2/2012)
Sean Lange (5/2/2012)
patrickmcginnis59 (5/2/2012)
Sean Lange (5/2/2012)
Well unless you are doing on the most god awful things I have seen some sql people do...which is to create a temp table and then call a stored proc which depends on it being run within the same connection. That will work but it is an absolutely hideous idea. This is one of the rare times I am totally confident in using an absolute in sql.NEVER NEVER NEVER use temp tables inside of a proc that were declared elsewhere.
This seems like something you are fairly certain about. Is there a reason that this isn't a good idea?
Keep reading the follow up. It is more from personal experience of this kind of thing running haywire than anything else.
Well I read your follow up, but I was looking more for some discussion on this practice itself. I can see some negatives myself but I can see them being handy when declared in one routine, then used in another routine that is called from the first routine that declared it, especially when using routines (separate procedures) to break up a process into more manageable pieces.
I think that is what Sean is getting at. Based on his own experience, he finds it to be something that should not be done. I brought out that I had done it, but it takes good design and documentation for it to work.
May 2, 2012 at 10:54 am
Lynn Pettis (5/2/2012)
patrickmcginnis59 (5/2/2012)
Sean Lange (5/2/2012)
patrickmcginnis59 (5/2/2012)
Sean Lange (5/2/2012)
Well unless you are doing on the most god awful things I have seen some sql people do...which is to create a temp table and then call a stored proc which depends on it being run within the same connection. That will work but it is an absolutely hideous idea. This is one of the rare times I am totally confident in using an absolute in sql.NEVER NEVER NEVER use temp tables inside of a proc that were declared elsewhere.
This seems like something you are fairly certain about. Is there a reason that this isn't a good idea?
Keep reading the follow up. It is more from personal experience of this kind of thing running haywire than anything else.
Well I read your follow up, but I was looking more for some discussion on this practice itself. I can see some negatives myself but I can see them being handy when declared in one routine, then used in another routine that is called from the first routine that declared it, especially when using routines (separate procedures) to break up a process into more manageable pieces.
I think that is what Sean is getting at. Based on his own experience, he finds it to be something that should not be done. I brought out that I had done it, but it takes good design and documentation for it to work.
+1
Yes indeed. In a small scenario it is probably not a big deal. However even a small scenario tends to grow into something massive like multiple nested procs with nested cursors and temp table he!! over time. This is the type of thing that can turn into a swirling bowl pasta very easily if not kept under control. As Lynn said, if you do anything like this make sure you document it very well.
Now that we have completely hijacked this thread...
_______________________________________________________________
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/
May 3, 2012 at 4:44 am
njdevils39 (5/2/2012)
So, in my post to go off from this 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
What would you suggest so I can get just one row for the Data from not only the DDL and Sample Data I provided, but for any employee's rate history, but the return is in one row.
@njdevils39: Please provide a set of sample output data, matching the DDL and Sample data you provided so far. For I do not see what you mean by this description.
May 4, 2012 at 8:02 am
R.P.Rozema (5/3/2012)
njdevils39 (5/2/2012)
So, in my post to go off from this 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
What would you suggest so I can get just one row for the Data from not only the DDL and Sample Data I provided, but for any employee's rate history, but the return is in one row.
@njdevils39: Please provide a set of sample output data, matching the DDL and Sample data you provided so far. For I do not see what you mean by this description.
Ok so this applies to Sean as well:
Here is my DDL and Sample Data File:
-- 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'
Sample output should be whatever the last statement in the Sample Data File is, but it will have to be returned in one row for any EMP_ID I input.
May 4, 2012 at 8:29 am
njdevils39 (5/4/2012)
R.P.Rozema (5/3/2012)
njdevils39 (5/2/2012)
So, in my post to go off from this 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
What would you suggest so I can get just one row for the Data from not only the DDL and Sample Data I provided, but for any employee's rate history, but the return is in one row.
@njdevils39: Please provide a set of sample output data, matching the DDL and Sample data you provided so far. For I do not see what you mean by this description.
Ok so this applies to Sean as well:
Here is my DDL and Sample Data File:
-- 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'
Sample output should be whatever the last statement in the Sample Data File is, but it will have to be returned in one row for any EMP_ID I input.
I have a very simple question. You have the sample data (you posted it here). You know what the you want the output to look like, you have described it verbally. Why can't you manually take this sample data, and generate us the expected output in a table format so we can actually see what we should be getting?
May 4, 2012 at 9:23 am
Lynn Pettis (5/4/2012)
njdevils39 (5/4/2012)
R.P.Rozema (5/3/2012)
njdevils39 (5/2/2012)
So, in my post to go off from this 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
What would you suggest so I can get just one row for the Data from not only the DDL and Sample Data I provided, but for any employee's rate history, but the return is in one row.
@njdevils39: Please provide a set of sample output data, matching the DDL and Sample data you provided so far. For I do not see what you mean by this description.
Ok so this applies to Sean as well:
Here is my DDL and Sample Data File:
-- 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'
Sample output should be whatever the last statement in the Sample Data File is, but it will have to be returned in one row for any EMP_ID I input.
I have a very simple question. You have the sample data (you posted it here). You know what the you want the output to look like, you have described it verbally. Why can't you manually take this sample data, and generate us the expected output in a table format so we can actually see what we should be getting?
For WHitehead, Molly This is what the output should be for her respective XML:
<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>
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
For Clinton, Gentry:
<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>
select 1, 5, 'Gentry, Clinton A', 'Silver Manor', 'Administrative', NULL, '04/05/2009', '11.44', '11.73'
May 7, 2012 at 8:02 am
njdevils39 (5/4/2012)
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
For Clinton, Gentry:
<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>
select 1, 5, 'Gentry, Clinton A', 'Silver Manor', 'Administrative', NULL, '04/05/2009', '11.44', '11.73'
This is EXACTLY what I get running the stored proc I posted above with the sample data.
_______________________________________________________________
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/
May 7, 2012 at 8:13 am
Sean Lange (5/7/2012)
njdevils39 (5/4/2012)
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
For Clinton, Gentry:
<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>
select 1, 5, 'Gentry, Clinton A', 'Silver Manor', 'Administrative', NULL, '04/05/2009', '11.44', '11.73'
This is EXACTLY what I get running the stored proc I posted above with the sample data.
Please re-post that code if you can.
May 7, 2012 at 8:19 am
njdevils39 (5/7/2012)
Sean Lange (5/7/2012)
njdevils39 (5/4/2012)
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
For Clinton, Gentry:
<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>
select 1, 5, 'Gentry, Clinton A', 'Silver Manor', 'Administrative', NULL, '04/05/2009', '11.44', '11.73'
This is EXACTLY what I get running the stored proc I posted above with the sample data.
Please re-post that code if you can.
http://www.sqlservercentral.com/Forums/Topic1268991-391-16.aspx#bm1293965
_______________________________________________________________
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/
May 7, 2012 at 8:23 am
Sean Lange (5/7/2012)
njdevils39 (5/7/2012)
Sean Lange (5/7/2012)
njdevils39 (5/4/2012)
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
For Clinton, Gentry:
<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>
select 1, 5, 'Gentry, Clinton A', 'Silver Manor', 'Administrative', NULL, '04/05/2009', '11.44', '11.73'
This is EXACTLY what I get running the stored proc I posted above with the sample data.
Please re-post that code if you can.
http://www.sqlservercentral.com/Forums/Topic1268991-391-16.aspx#bm1293965
I am not getting any data returned though. Its just a blank table.
May 7, 2012 at 8:25 am
Here is my code when I involve the actual, not temp tables.
ALTER PROCEDURE [dbo].[WEB_HR_GetRateHistory]
(
@EMP_ID INT
)
AS BEGIN
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,
count(*) over (partition by o.emp_id) as MAX_ROWNUM,
x.r.value('@date_changed','DATETIME') AS DATE_CHANGED,
x.r.value('@rate','DECIMAL(10,5)') AS RATE
from EMPLOYEE_OTHER o
cross apply o.rate_history.nodes('/Rates/Rate') AS x(r)
)
SELECT rLatest.ROWNUM,
e.EMP_ID,
e.EMP_NAME,
e.EMP_ORGANIZATION,
e.EMP_DEPARTMENT,
e.EMP_DT_TERMINATION,
rLatest.DATE_CHANGED,
rOldest.RATE AS FROM_PAY_RATE,
rLatest.RATE AS TO_PAY_RATE
FROM EMPLOYEE e
left outer join cteRates rLatest on (rLatest.EMP_ID = e.EMP_ID and rLatest.ROWNUM = 1)
left outer join cteRates rOldest on (rOldest.EMP_ID = e.EMP_ID and rOldest.ROWNUM = rOldest.MAX_ROWNUM)
END
When I input an Emp_ID, its not returning any result.
May 7, 2012 at 9:11 am
OK using real tables here is the code...still works fine.
alter PROCEDURE [dbo].[WEB_HR_GetRateHistory]
(
@EMP_ID INT
)
AS BEGIN
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,
count(*) over (partition by o.emp_id) as MAX_ROWNUM,
x.r.value('@date_changed','DATETIME') AS DATE_CHANGED,
x.r.value('@rate','DECIMAL(10,5)') AS RATE
from EMPLOYEE_OTHER o
cross apply o.rate_history.nodes('/Rates/Rate') AS x(r)
)
SELECT rLatest.ROWNUM,
e.EMP_ID,
e.EMP_NAME,
e.EMP_ORGANIZATION,
e.EMP_DEPARTMENT,
e.EMP_DT_TERMINATION,
rLatest.DATE_CHANGED,
rOldest.RATE AS FROM_PAY_RATE,
rLatest.RATE AS TO_PAY_RATE
FROM EMPLOYEE e
left outer join cteRates rLatest on (rLatest.EMP_ID = e.EMP_ID and rLatest.ROWNUM = 1)
left outer join cteRates rOldest on (rOldest.EMP_ID = e.EMP_ID and rOldest.ROWNUM = rOldest.MAX_ROWNUM)
where e.EMP_ID = @EMP_ID
END
go
CREATE TABLE EMPLOYEE
(
EMP_ID INT,
EMP_NAME VARCHAR(MAX),
EMP_ORGANIZATION VARCHAR(MAX),
EMP_DEPARTMENT VARCHAR(MAX),
EMP_DT_TERMINATION DATETIME
)
CREATE TABLE EMPLOYEE_OTHER
(
EMP_ID INT,
RATE_HISTORY XML
)
INSERT INTO EMPLOYEE
(
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_OTHER
(
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>'
)
go
exec WEB_HR_GetRateHistory @EMP_ID = 3
exec WEB_HR_GetRateHistory @EMP_ID = 5
Notice I removed the xml variable because it isn't used and put back the where clause. There is nothing further anybody can do to help here. The ddl and sample data provided will produce these results which are what you said you wanted. Given the information provided the code I provided returns the exact results you said you are looking for.
_______________________________________________________________
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/
May 7, 2012 at 12:42 pm
Sean Lange (5/7/2012)
OK using real tables here is the code...still works fine.
alter PROCEDURE [dbo].[WEB_HR_GetRateHistory]
(
@EMP_ID INT
)
AS BEGIN
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,
count(*) over (partition by o.emp_id) as MAX_ROWNUM,
x.r.value('@date_changed','DATETIME') AS DATE_CHANGED,
x.r.value('@rate','DECIMAL(10,5)') AS RATE
from EMPLOYEE_OTHER o
cross apply o.rate_history.nodes('/Rates/Rate') AS x(r)
)
SELECT rLatest.ROWNUM,
e.EMP_ID,
e.EMP_NAME,
e.EMP_ORGANIZATION,
e.EMP_DEPARTMENT,
e.EMP_DT_TERMINATION,
rLatest.DATE_CHANGED,
rOldest.RATE AS FROM_PAY_RATE,
rLatest.RATE AS TO_PAY_RATE
FROM EMPLOYEE e
left outer join cteRates rLatest on (rLatest.EMP_ID = e.EMP_ID and rLatest.ROWNUM = 1)
left outer join cteRates rOldest on (rOldest.EMP_ID = e.EMP_ID and rOldest.ROWNUM = rOldest.MAX_ROWNUM)
where e.EMP_ID = @EMP_ID
END
go
CREATE TABLE EMPLOYEE
(
EMP_ID INT,
EMP_NAME VARCHAR(MAX),
EMP_ORGANIZATION VARCHAR(MAX),
EMP_DEPARTMENT VARCHAR(MAX),
EMP_DT_TERMINATION DATETIME
)
CREATE TABLE EMPLOYEE_OTHER
(
EMP_ID INT,
RATE_HISTORY XML
)
INSERT INTO EMPLOYEE
(
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_OTHER
(
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>'
)
go
exec WEB_HR_GetRateHistory @EMP_ID = 3
exec WEB_HR_GetRateHistory @EMP_ID = 5
Notice I removed the xml variable because it isn't used and put back the where clause. There is nothing further anybody can do to help here. The ddl and sample data provided will produce these results which are what you said you wanted. Given the information provided the code I provided returns the exact results you said you are looking for.
Ok, now it works fine for every employee. But can you tell me if you can, how to do it with this 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
Viewing 15 posts - 166 through 180 (of 194 total)
You must be logged in to reply to this topic. Login to reply