April 6, 2012 at 10:52 am
njdevils39 (4/6/2012)
...Where should I put this query in my original SQL CODE? Note: Include EMP_NAME, EMP_ORGANIZATION, EMP_DEPARTMENT and EMP_DT_TERMINATION.
I have no idea.
It would make sense to place it somewhere after #EMPLOYEE2 and #EMPLOYEE_OTHER2 are created and populated and before those temp tables are dropped again...
I don't understand your note. Is this a question or a requirement or a comment or request or an order?
We can spoon feed you. But you'll need to swallow by yourself. Otherwise it can easily get painful.
April 6, 2012 at 10:55 am
LutzM (4/6/2012)
njdevils39 (4/6/2012)
...Where should I put this query in my original SQL CODE? Note: Include EMP_NAME, EMP_ORGANIZATION, EMP_DEPARTMENT and EMP_DT_TERMINATION.
I have no idea.
It would make sense to place it somewhere after #EMPLOYEE2 and #EMPLOYEE_OTHER2 are created and populated and before those temp tables are dropped again...
I don't understand your note. Is this a question or a requirement or a comment or request or an order?
We can spoon feed you. But you'll need to swallow by yourself. Otherwise it can easily get painful.
Here is my code now with your CTE statement, but it won't run at all:
-- 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;
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.EMP_NAME,
c1.EMP_ORGANIZATION,
c1.EMP_DEPARTMENT,
c1.EMP_DT_TERMINATION,
c1.DATE_CHANGED,
c2.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
INNER JOIN cteRates c2 ON c1.EMP_ID=c2.EMP_ID AND c1.RowNum=1 AND c2.RowNum=2
April 6, 2012 at 10:58 am
njdevils39 (4/6/2012)
LutzM (4/6/2012)
njdevils39 (4/6/2012)
...Where should I put this query in my original SQL CODE? Note: Include EMP_NAME, EMP_ORGANIZATION, EMP_DEPARTMENT and EMP_DT_TERMINATION.
I have no idea.
It would make sense to place it somewhere after #EMPLOYEE2 and #EMPLOYEE_OTHER2 are created and populated and before those temp tables are dropped again...
I don't understand your note. Is this a question or a requirement or a comment or request or an order?
We can spoon feed you. But you'll need to swallow by yourself. Otherwise it can easily get painful.
Here is my code now with your CTE statement, but it won't run at all:
-- 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;
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.EMP_NAME,
c1.EMP_ORGANIZATION,
c1.EMP_DEPARTMENT,
c1.EMP_DT_TERMINATION,
c1.DATE_CHANGED,
c2.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
INNER JOIN cteRates c2 ON c1.EMP_ID=c2.EMP_ID AND c1.RowNum=1 AND c2.RowNum=2
Please define "won't run at all." That can mean so many different things to different people.
April 6, 2012 at 10:59 am
It's always great to get a reply like "it won't run"...
You might want to take the time and read the error message SQL Server is kind enough to provide.
@Lynn: columns referenced in the outer query but not included in the CTE.
April 6, 2012 at 11:00 am
njdevils39 (4/6/2012)
LutzM (4/6/2012)
njdevils39 (4/6/2012)
...Where should I put this query in my original SQL CODE? Note: Include EMP_NAME, EMP_ORGANIZATION, EMP_DEPARTMENT and EMP_DT_TERMINATION.
I have no idea.
It would make sense to place it somewhere after #EMPLOYEE2 and #EMPLOYEE_OTHER2 are created and populated and before those temp tables are dropped again...
I don't understand your note. Is this a question or a requirement or a comment or request or an order?
We can spoon feed you. But you'll need to swallow by yourself. Otherwise it can easily get painful.
Here is my code now with your CTE statement, but it won't run at all:
-- 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;
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.EMP_NAME,
c1.EMP_ORGANIZATION,
c1.EMP_DEPARTMENT,
c1.EMP_DT_TERMINATION,
c1.DATE_CHANGED,
c2.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
INNER JOIN cteRates c2 ON c1.EMP_ID=c2.EMP_ID AND c1.RowNum=1 AND c2.RowNum=2
These are the error messages"
Msg 207, Level 16, State 1, Line 81
Invalid column name 'EMP_NAME'.
Msg 207, Level 16, State 1, Line 82
Invalid column name 'EMP_ORGANIZATION'.
Msg 207, Level 16, State 1, Line 83
Invalid column name 'EMP_DEPARTMENT'.
Msg 207, Level 16, State 1, Line 84
Invalid column name 'EMP_DT_TERMINATION'.
April 6, 2012 at 12:19 pm
So given the error message what do YOU think the problem is? This is not some voodoo uber awesome sql thing hidden away. It should take anybody with basic sql knowledge about 2 seconds to identify.
It is readily apparent that if you can't figure that out you don't even begin to understand what this code is doing. What are you going to do when somebody, like your boss, asks you what this code is doing? Or asks you to change it? What happens when it takes you 2 months to produce something very similar because you don't understand what you did this time? Keep in mind it will be YOU that gets called at 3am when the users have problems and YOU have to fix the problem.
_______________________________________________________________
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 6, 2012 at 12:50 pm
Sean Lange (4/6/2012)
So given the error message what do YOU think the problem is? This is not some voodoo uber awesome sql thing hidden away. It should take anybody with basic sql knowledge about 2 seconds to identify.It is readily apparent that if you can't figure that out you don't even begin to understand what this code is doing. What are you going to do when somebody, like your boss, asks you what this code is doing? Or asks you to change it? What happens when it takes you 2 months to produce something very similar because you don't understand what you did this time? Keep in mind it will be YOU that gets called at 3am when the users have problems and YOU have to fix the problem.
Ohhh, ohhhh, ohhhhh! I know! (Sorry best imitation I know for Horshack).
April 6, 2012 at 1:08 pm
Lynn Pettis (4/6/2012)
Sean Lange (4/6/2012)
So given the error message what do YOU think the problem is? This is not some voodoo uber awesome sql thing hidden away. It should take anybody with basic sql knowledge about 2 seconds to identify.It is readily apparent that if you can't figure that out you don't even begin to understand what this code is doing. What are you going to do when somebody, like your boss, asks you what this code is doing? Or asks you to change it? What happens when it takes you 2 months to produce something very similar because you don't understand what you did this time? Keep in mind it will be YOU that gets called at 3am when the users have problems and YOU have to fix the problem.
Ohhh, ohhhh, ohhhhh! I know! (Sorry best imitation I know for Horshack).
+1 That was awesome!!!
_______________________________________________________________
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 8:36 am
I apologize and I am a bit surprised this topic went this long. I blame myself, but I am trying new things and I am being an A-Hole doing it. Here is my next nonsense code for my Stored Procedure, I went with what I did in the DDL, but it does not work:
ALTER PROCEDURE [dbo].[WEB_HR_GetRateHistory]
(
@ORG_LEVEL_ID INT,
@EMP_ID INT
)
AS
BEGIN
DECLARE @x XML;
CREATE TABLE #RATE_HISTORY
(
EMP_ID INT,
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_ID,
EMP_NAME,
EMP_ORGANIZATION,
EMP_DEPARTMENT,
EMP_DT_TERMINATION,
DATE_CHANGED,
FROM_PAY_RATE,
TO_PAY_RATE
)
SELECT * from
(
SELECT (ROW_NUMBER() over(partition by e.EMP_ID order by x.r.value('@date_changed','DATETIME') desc)) as RowNum,
e.emp_name,
e.emp_organization,
e.emp_department,
e.emp_dt_termination,
DATE_CHANGED = (select x.r.value('@date_changed','DATETIME')) from employee_other),
FROM_PAY_RATE = (select x.r.value('@rate','DECIMAL(10,5)')) from employee_other),
TO_PAY_RATE = (select x.r.value('@rate','DECIMAL(10,5)')) from employee_other)
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
CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)
)
WHERE ROWNUM = 1 and FROM_PAY_RATE = FINAL(ROW)
select * from #RATE_HISTORY
END
April 9, 2012 at 8:45 am
You still haven't learned that we don't sit around a glass ball and figure out what "it doesn't work" means in your case.
I'm also surprised you continue to use a solution you know that doesn't work.
Since it seems like you tend to ignore any other alternative offered to you (e.g. the one I did a few posts back) I decide to leave this thread to others. I'm out.
April 9, 2012 at 8:58 am
I really don't feel like going through all the previous posts, so I'm just going to ask, have we been given all the requested information in a readily consumable format or are we still waiting for that information?
April 9, 2012 at 9:03 am
All I need is one column value and that the FROM_PAY_RATE column. IN my DDL post, everything works, but not the FROM_PAY_RATE. It needs to correspond with the final row of the respective Employee record.
April 9, 2012 at 9:08 am
I have to agree with Lynn. How about we take this approach, you go dig through the 100+ posts of mostly nonsense and find the ddl and sample data in a nice clean format. Post that again along with what you want for desired output. A simple and clean explanation would also be a big help.
_______________________________________________________________
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 9:16 am
Sean Lange (4/9/2012)
I have to agree with Lynn. How about we take this approach, you go dig through the 100+ posts of mostly nonsense and find the ddl and sample data in a nice clean format. Post that again along with what you want for desired output. A simple and clean explanation would also be a big help.
Here is my nonsense Sample Data and DDL, but the FROM_PAY_RATE column matches the To_PAY_RATE column. While that may be true for some employees who have 1 row in the XML file, it will be different in that column.
Here are three XML Files, two with multiple rows and one with one row.
<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>
Obviously in this file, the two rate columns will match
<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>
In this one the From_Pay_Rate has to be 11.44 and the TO_PAY_RATE should be 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 one will remain as is.
-- 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
April 9, 2012 at 12:38 pm
By the way, I also do not work yet or get paid. I am an unpaid intern learning to advance my programming knowledge.
Viewing 15 posts - 106 through 120 (of 194 total)
You must be logged in to reply to this topic. Login to reply