Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# tsql programming

Posted on 2013-05-13
Medium Priority
195 Views
I have a table defined as such:

And want the result set below:

How would I code this in t-sql?  This is basically a small workflow where the higher rank value is higher up the approval chain.  In the result set I just want to see for Bob that his approver Email is that of Susie and so on until the last Employee (Ned) who would not have any ApproverEmail value.  There may not only be 4 total records.

Thanks!
0
Question by:d1cjm1ex
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2
• 2

LVL 25

Accepted Solution

chaau earned 1000 total points
ID: 39163500
``````Select e.Emp_ID, e.Emp_Nam, e.Rank, a.Email AS ApproverEmail
FROM Table1 e LEFT JOIN Table1 a
ON e.Rank + 1 = a.Rank
``````

Please note, that this solution will only work if you have an approver exactly one Rank up. If your data has some missing Ranks (i.e. 1,2,3,4,7,8,9, etc) and you need that the approver for #4 to be #7, the SQL will be slightly different. please let us know
0

LVL 49

Assisted Solution

PortletPaul earned 1000 total points
ID: 39163531
to avoid possible gaps in the Rank values you can substitute a row_number() calculation like this
``````;with
CTE AS (
SELECT
e.Emp_ID
, e.Emp_Name
, e.Rank
, e.Email
, row_number() over (order by rank) as row_ref
FROM Employees e
)
SELECT
e.Emp_ID
, e.Emp_Name
, e.Rank
, a.Email AS ApproverEmail
FROM CTE e
LEFT JOIN CTE a ON e.Row_ref + 1 = a.Row_Ref
``````
http://sqlfiddle.com/#!3/7dd0b/8
0

LVL 25

Expert Comment

ID: 39163607
Please note that both of our solutions (PortletPaul's and mine) will not work in case if have bad data.

If you have entries with duplicate ranks mine will duplicate, PortletPaul's will assign a wrong Approver.
If you have gaps, PortletPaul's will work, mine will not find an entry.

@PortletPaul: I have added a few records to your SQL Fiddle, if you don't mind
0

LVL 49

Expert Comment

ID: 39163674
@chaau, no problem, never an issue, I think it's a great idea, helps arrive at the best outcome

Paul

{+edit} but if changing the sqlfiddle 'schema' the url changes :(
0

LVL 49

Expert Comment

ID: 39163696
kudos to chaau for the identification of a weakness. dense_rank() to the rescue (I think)  http://sqlfiddle.com/#!3/a9b6a/1
``````;with
CTE AS (
SELECT
e.Emp_ID
, e.Emp_Name
, e.Rank
, e.Email
, dense_rank() over (order by rank) as row_ref
FROM Employees e
)
SELECT
e.Emp_ID
, e.Emp_Name
, e.Rank
, a.Email AS ApproverEmail
FROM CTE e
LEFT JOIN CTE a ON e.Row_ref + 1 = a.Row_Ref
``````
and purely because it may be useful at some point, a reversal:
``````/* and as it might be useful the reverse */
SELECT
e.Emp_ID
, e.Emp_Name
, e.Rank
, coalesce(a.Email, e.Email) AS ApprovalByEmail
FROM Employees e
LEFT JOIN Employees a ON e.Rank - 1 = a.Rank
``````
0

Author Comment

ID: 39166848
Thanks guys.  So my data could be "bad" in that there could be multiple 2's and there could be gaps. Gaps would be truly bad data and should in theory not exist so as long as the code could handle the multiple records with the same rank, it should be ok.

I will test out this code tomorrow.
0

Author Closing Comment

ID: 39167601
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error â€œConnecting to the Integration Services service on the computer failed with the following error: 'The specified service â€¦
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
###### Suggested Courses
Course of the Month7 days, 6 hours left to enroll