May 7, 2012 at 12:48 pm
What is wrong with what you posted? One thing I would do differently is don't bother creating temp table, then filling the temp table, then select all the data out of the temp table. Why not just select the 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 12:57 pm
Sean Lange (5/7/2012)
What is wrong with what you posted? One thing I would do differently is don't bother creating temp table, then filling the temp table, then select all the data out of the temp table. Why not just select the data?
No Problem, just want an extra code where something like that would work. What would the code look like though?
May 7, 2012 at 1:26 pm
njdevils39 (5/7/2012)
Sean Lange (5/7/2012)
What is wrong with what you posted? One thing I would do differently is don't bother creating temp table, then filling the temp table, then select all the data out of the temp table. Why not just select the data?No Problem, just want an extra code where something like that would work. What would the code look like though?
Not sure what 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 1:52 pm
njdevils39 (5/7/2012)
...
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
We can't make your query work for you, because that code will not produce the output that you requested. Let me try to show you what's wrong with it. In the next query window I've reformatted your query and put in a unique table alias for each table instance that you refer to in your query:
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 eo1
CROSS APPLY eo1.rate_history.nodes('/Rates/Rate') AS x(r)
),
FROM_PAY_RATE = (
SELECT
x.r.value('@rate','DECIMAL(10,5)')
FROM employee_other eo2
CROSS APPLY eo2.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 eo3
CROSS APPLY eo3.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
INNER JOIN employee_other eo ON e.emp_id = eo.emp_id
WHERE e.emp_id = @emp_id
AND e.emp_dt_termination IS NULL
AND e.emp_organization_id = @ORG_LEVEL_ID
Do you see how your query refers to 4 unrelated rows in the employee_other table? i.e. your query will go through all rows in the employee_other table 4 times: once to find the highest date_changed value in any Rate node in any row (eo1). Then once to find all rate values from all rows (eo2). Next once more to find all rate values from all rows (eo3). And finally it will once more try to find all rows that match on emp_id with an employee row's emp_id (eo). Your query lacks a way to make sure that eo's row is the same row as referred to by eo1, eo2 and eo3. In other words, we could make the query syntactically correct (i.e. accepted by SQL server without an error message), but it will not produce the results you asked for.
The query that we've given you does produce the requested results by first finding per emp_id the latest and the oldest rate. This is done in the Common Table Expression (= cteRates) by numbering all rates from all xml's having the same emp_id ordered by the change_date values. The row having ROWNUM 1 will be the most recent rate for this emp_id. Every row in cteRates also includes the total number of rates (count(*)) found for this emp_id, so we can later easily pick the one row where ROWNUM equals the total number of rates for this emp_id, i.e. the row that has the oldest change_date. Finally, in the main query the pieces are put together by listing all employee rows and for each join the cteRates row where ROWNUM equals 1 joined on emp_id with that employee row. This retrieves the most recent rate and change_date values. Furthermore the row from cteRates where ROWNUM equals the MAX_ROWNUM is joined on emp_id too, to retrieve the oldest rate for the employee.
May 7, 2012 at 2:29 pm
Sean Lange (5/7/2012)
njdevils39 (5/7/2012)
Sean Lange (5/7/2012)
What is wrong with what you posted? One thing I would do differently is don't bother creating temp table, then filling the temp table, then select all the data out of the temp table. Why not just select the data?No Problem, just want an extra code where something like that would work. What would the code look like though?
Not sure what you are looking for?
Never mind, but how do I get rid of the first two columns, so it would not return the ROWNUM and the EMP_ID. So, the return would be two less columns.
May 7, 2012 at 2:32 pm
njdevils39 (5/7/2012)
Sean Lange (5/7/2012)
njdevils39 (5/7/2012)
Sean Lange (5/7/2012)
What is wrong with what you posted? One thing I would do differently is don't bother creating temp table, then filling the temp table, then select all the data out of the temp table. Why not just select the data?No Problem, just want an extra code where something like that would work. What would the code look like though?
Not sure what you are looking for?
Never mind, but how do I get rid of the first two columns, so it would not return the ROWNUM and the EMP_ID. So, the return would be two less columns.
uummm...change the select * to only whatever columns you want returned?
_______________________________________________________________
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 2:37 pm
Sean Lange (5/7/2012)
njdevils39 (5/7/2012)
Sean Lange (5/7/2012)
njdevils39 (5/7/2012)
Sean Lange (5/7/2012)
What is wrong with what you posted? One thing I would do differently is don't bother creating temp table, then filling the temp table, then select all the data out of the temp table. Why not just select the data?No Problem, just want an extra code where something like that would work. What would the code look like though?
Not sure what you are looking for?
Never mind, but how do I get rid of the first two columns, so it would not return the ROWNUM and the EMP_ID. So, the return would be two less columns.
uummm...change the select * to only whatever columns you want returned?
Sorry to be a pain, but how should it look with this code.
ALTER PROCEDURE [dbo].[WEB_HR_GetRateHistory]
(
@ORG_LEVEL_ID INT,
@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,
convert(varchar(10), x.r.value('@date_changed','DATETIME'), 101) AS DATE_CHANGED,
x.r.value('@rate','DECIMAL(10,2)') 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,
((rLatest.RATE) - (rOldest.RATE)) AS CHANGED_BY
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 and @ORG_LEVEL_ID = e.emp_organization_id
END
May 7, 2012 at 2:47 pm
You seem have me lost...
Sorry to be a pain, but how should it look with this code.
What does that mean? What is the question?
Did you notice that you have added another column that is not in your ddl?
_______________________________________________________________
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 2:49 pm
Sean Lange (5/7/2012)
You seem have me lost...Sorry to be a pain, but how should it look with this code.
What does that mean? What is the question?
Did you notice that you have added another column that is not in your ddl?
My question is that how do I get rid of ROWNUM and EMP_ID from the return set and how would the code look like? And YES I added the CHANGED_BY COLUMN, which has no relevance to my question.
May 7, 2012 at 2:54 pm
njdevils39 (5/7/2012)
Sean Lange (5/7/2012)
You seem have me lost...Sorry to be a pain, but how should it look with this code.
What does that mean? What is the question?
Did you notice that you have added another column that is not in your ddl?
My question is that how do I get rid of ROWNUM and EMP_ID from the return set and how would the code look like? And YES I added the CHANGED_BY COLUMN, which has no relevance to my question.
I don't mean to sound snarky but you seriously don't know how to limit the columns in a select statement?
Here is the select statement, to remove the columns you don't want, remove them from the select statement.
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,
((rLatest.RATE) - (rOldest.RATE)) AS CHANGED_BY
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 and @ORG_LEVEL_ID = e.emp_organization_id
The second half of your where clause is what I was referring to by new columns. That column is not the posted ddl.
and @ORG_LEVEL_ID = e.emp_organization_id
_______________________________________________________________
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 3:01 pm
Sean Lange (5/7/2012)
njdevils39 (5/7/2012)
Sean Lange (5/7/2012)
You seem have me lost...Sorry to be a pain, but how should it look with this code.
What does that mean? What is the question?
Did you notice that you have added another column that is not in your ddl?
My question is that how do I get rid of ROWNUM and EMP_ID from the return set and how would the code look like? And YES I added the CHANGED_BY COLUMN, which has no relevance to my question.
I don't mean to sound snarky but you seriously don't know how to limit the columns in a select statement?
Here is the select statement, to remove the columns you don't want, remove them from the select statement.
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,
((rLatest.RATE) - (rOldest.RATE)) AS CHANGED_BY
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 and @ORG_LEVEL_ID = e.emp_organization_id
The second half of your where clause is what I was referring to by new columns. That column is not the posted ddl.
and @ORG_LEVEL_ID = e.emp_organization_id
Merci Maku
May 7, 2012 at 3:03 pm
You're welcome. Does this mean you finally have your 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/
May 15, 2012 at 2:52 pm
Sean Lange (5/7/2012)
You're welcome. Does this mean you finally have your answer? 😛
I assume by your silence that you did in fact get your issue resolved. It is somewhat customary around here to at least let the people who volunteer their time to help you know that your issue is taken care of. Hopefully everything is what you needed and all is well.
_______________________________________________________________
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 16, 2012 at 8:29 am
Sean Lange (5/15/2012)
Sean Lange (5/7/2012)
You're welcome. Does this mean you finally have your answer? 😛I assume by your silence that you did in fact get your issue resolved. It is somewhat customary around here to at least let the people who volunteer their time to help you know that your issue is taken care of. Hopefully everything is what you needed and all is well.
Yes everything works fine
May 16, 2012 at 8:31 am
njdevils39 (5/16/2012)
Sean Lange (5/15/2012)
Sean Lange (5/7/2012)
You're welcome. Does this mean you finally have your answer? 😛I assume by your silence that you did in fact get your issue resolved. It is somewhat customary around here to at least let the people who volunteer their time to help you know that your issue is taken care of. Hopefully everything is what you needed and all is well.
Yes everything works fine
Cool. Thanks for letting us know.
_______________________________________________________________
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/
Viewing 15 posts - 181 through 194 (of 194 total)
You must be logged in to reply to this topic. Login to reply