March 19, 2012 at 10:42 am
This is my join statement, but the rate_history columns has to be broken down into 4 columns (Date_Changed, FROM_PAY_RATE, TO_PAY_RATE, CHANGED_BY_PERCENTAGE). How do I go about doing this with the cross apply reference Sean offered me?
ALTER PROCEDURE WEB_HR_GetRateHistory
(
@ORG_LEVEL_ID INT,
@EMP_ID INT
)
AS
BEGIN
SELECT em.emp_name, em.emp_organization, em.emp_department, eo.rate_history from
employee as em join
employee_other as eo
on
em.emp_id = eo.emp_id
END
March 19, 2012 at 10:59 am
I have suggested multiple times that the best way is to provide some details so people can help you. You seem to be unwilling to actually take 5 minutes of your time to read an article suggested by at least 3 people now who are offering to help you for free. Nobody can help you if you can't provide the details of the problem.
This is kind of like asking a math problem.
solve for x.
y = 13.
x = ?
Then you don't understand why nobody can answer. It is because you have not provided enough information to come up with an answer.
_______________________________________________________________
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/
March 19, 2012 at 12:55 pm
-Say From_PAY_Rate on March 1st would say 3.00 just to give in example
-Say To_Pay_Rate on latest date as it is shown in the XML file is 3.20 for example I need a final column to be the percentage of change or difference of change called (Changed_BY).
Give me both scenerios. So initial rate or RATE is FROM_PAY_RATE and latest date is To_PAY_RATE
March 19, 2012 at 1:00 pm
njdevils39 (3/19/2012)
-Say From_PAY_Rate on March 1st would say 3.00 just to give in example-Say To_Pay_Rate on latest date as it is shown in the XML file is 3.20 for example I need a final column to be the percentage of change or difference of change called (Changed_BY).
Give me both scenerios. So initial rate or RATE is FROM_PAY_RATE and latest date is To_PAY_RATE
I have patiently requested ddl and sample data to help you with your problem 3 times. During that time at least two other people have said the same thing. You continue to come back with demands for sample code that is impossible to create. I hope you can solve your issue. I am out.
_______________________________________________________________
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/
March 19, 2012 at 1:06 pm
Can we say it any plainer than this:
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
March 23, 2012 at 8:55 am
Here is my code so far: How should I continue?
ALTER PROCEDURE WEB_HR_GetRateHistory
(
@ORG_LEVEL_ID INT,
@EMP_ID INT
)
AS
BEGIN
CREATE TABLE #RATE_HISTORY
(
EMP_NAME VARCHAR(255),
EMP_ORGANIZATION VARCHAR(255)
EMP_DEPARTMENT VARCHAR(255),
DATE_CHANGED DATETIME,
FROM_PAY_RATE INT,
TO_PAY_RATE INT,
CHANGED_BY INT
)
INSERT INTO #RATE_HISTORY
(
EMP_NAME,
EMP_ORGANIZATION,
EMP_DEPARTMENT,
DATE_CHANGED,
FROM_PAY_RATE,
TO_PAY_RATE,
CHANGED_BY
)
(
SELECT em.emp_name, em.emp_organization, em.emp_department, FROM_PAY_RATE = (convert(xml(rate_history), rate, 101)) from
employee as em join
employee_other as eo
on
em.emp_id = eo.emp_id
where em.emp_id = @emp_id and emp_organization_id = @ORG_LEVEL_ID
)
END
March 23, 2012 at 9:08 am
DDL, SAMPLE DATA!!! READ THE ARTICLE THAT HAS BEEN SUGGESTED OVER AND OVER!!!!! This is not rocket surgery. Pay attention and listen to what the people who are trying to help you are saying.
_______________________________________________________________
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/
March 23, 2012 at 9:13 am
Yes I know, but how do I put that CROSS APPLY STATEMENT YOU GUYS SUGGESTED AND JOIN It with the employee table.
March 23, 2012 at 9:15 am
Start by providing us something to work with. DDL and Sample data. I can't write code for you without some tables.
_______________________________________________________________
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/
March 23, 2012 at 9:29 am
I want 4 columns based on not only this XML code, but all the XML codes in the column RATE_HISTORY which is in XML format.
DATE_CHANGED: The latest date in the XML
FROM PAY_RATE: INITIAL RATE
TO_PAY_RATE: LATEST DATE RATE
CHANGED_BY: DIFFERENCE OR PERCENTAGE OF CHANGE
I know there is a CROSS APPLY STATEMENT WHICH COMBINES ALL XML FILES IN THAT COLUMN YOU SUGGESTED.
SO FOR EXAMPLE IN THIS XML FILE:
FIRST ROW
date_changed = 09/21/2008 (I do not need the time)
FROM_PAY_RATE = 1730.77
TO_PAY_RATE = 1720.77
CHANGED BY = DIFFERENCE or PERCENTAGE of change from 1730.77 to 1730.77
SECOND ROW:
date_changed = 11/02/2008 (I do not need the time)
FROM_PAY_RATE = 1730.77
TO_PAY_RATE = 3461.54
CHANGED BY = DIFFERENCE or PERCENTAGE of change from 1730.77 to 3461.54
-
-
-
FINAL ROW FOR THIS XML FILE IS:
date_changed = 11/10/2011 (I do not need the time)
FROM_PAY_RATE = 1730.77
TO_PAY_RATE = 1720.78
CHANGED BY = DIFFERENCE or PERCENTAGE of change from 1720.78 to 1720.78
AND then on and on with each XML FILE in the EMPLOYEE_OTHER TABLE with the CROSS APPLY TABLE
<Rates>
<Rate date_changed="Nov 10 2011 7:28AM" not_active_since="07/27/2011" rate="1720.78" emp_pay_type="1" emp_work_hours="0" rate2="0.00" rate3="0.00" benefit_hours="8" exempt_ind="Y" />
<Rate date_changed="Nov 10 2011 7:19AM" not_active_since="07/27/2011" rate="1720.78" emp_pay_type="1" emp_work_hours="40" rate2="0.00" rate3="0.00" benefit_hours="8" exempt_ind="Y" />
<Rate date_changed="Nov 10 2011 6:38AM" not_active_since="07/27/2011" rate="1720.78" emp_pay_type="1" emp_work_hours="0" rate2="0.00" rate3="0.00" benefit_hours="8" exempt_ind="Y" />
<Rate date_changed="Nov 10 2011 6:33AM" not_active_since="07/27/2011" rate="1720.78" emp_pay_type="1" emp_work_hours="40" rate2="0.00" rate3="0.00" benefit_hours="8" exempt_ind="Y" />
<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>
March 23, 2012 at 9:38 am
I can imagine you are getting frustrated. If you can post ddl and sample data you will get some help. You are obviously too lazy to actually that article to see what that means so I will try to help.
ddl = data definition language. In plain english that means we need create table statements.
Sample data - in plain english this means we need insert statements so we have some sample data in the table(s) created above.
Once a sql developer has tables and data they can begin to query that data. You have presented over and over and over what you want to query results to be but you have repeatedly ignored multiple people asking for you to put in some effort to help us to help you. Nobody can write sql against tables and data that don't exist.
If you provide ddl and sample data you will find that LOTS AND LOTS of people around here will jump in with tested and fast code really quickly. If you insist on not providing this information you will continue to be frustrated and not get any benefit from the community around here.
The choice is yours.
_______________________________________________________________
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/
March 23, 2012 at 9:44 am
DDL STATEMENT:
CREATE TABLE #RATE_HISTORY
(
EMP_NAME VARCHAR(255),
EMP_ORGANIZATION VARCHAR(255)
EMP_DEPARTMENT VARCHAR(255),
DATE_CHANGED DATETIME,
FROM_PAY_RATE INT,
TO_PAY_RATE INT,
CHANGED_BY INT
)
SAMPLE DATA:
INSERT INTO #RATE_HISTORY
(
EMP_NAME,
EMP_ORGANIZATION,
EMP_DEPARTMENT,
DATE_CHANGED,
FROM_PAY_RATE,
TO_PAY_RATE,
CHANGED_BY
)
(
SELECT em.emp_name, em.emp_organization, em.emp_department, eo.DATE_CHANGED = READ_ABOVE, eo.FROM_PAY_RATE = READ_ABOVE, eo.TO_PAY_RATE = READ_ABOVE, eo.CHANGED BY = READ_ABOVE
employee as em join
FROM
employee_other as eo
on
em.emp_id = eo.emp_id
where em.emp_id = @emp_id and emp_organization_id = @ORG_LEVEL_ID
March 23, 2012 at 9:50 am
SAMPLE DATA, should include data!
We don't have the data in the table your are selecting from (actually we don't have any of your tables :-D)
Please follow the link at the bottom of my signature.
March 23, 2012 at 9:51 am
You do know that we are volunteers right??? You have not even thought about considering somebody else's ability to help you. Look at that insert you posted. I don't have the employee or employee_other. Think about what somebody else might need to start writing this query. I have completely run out of patience. I wish you luck.
_______________________________________________________________
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/
March 23, 2012 at 10:05 am
This is the easy sample data query:
select em.emp_name, em.emp_organization, em.emp_department, eo.rate_history
from
employee as em
join
employee_other as eo
on
em.emp_id = eo.emp_id
Now rate_history column is in XML FORMAT in the employee_other table for all employees in the employee table once you complete join statement.
I gave you guys an example of the first XML FILE In the rate_history.
The 4 columns I provided above or in previous posts is what I need the data to look like.
Viewing 15 posts - 16 through 30 (of 194 total)
You must be logged in to reply to this topic. Login to reply