Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

tsql programming

I have a table defined as such:
Source Data

And want the result set below:
Result Set
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
d1cjm1ex
Asked:
d1cjm1ex
  • 3
  • 2
  • 2
2 Solutions
 
chaauCommented:
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

Open in new window


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
 
PaulCommented:
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

Open in new window

http://sqlfiddle.com/#!3/7dd0b/8
0
 
chaauCommented:
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.

Please advise how good (or rather, how bad) is your data.

@PortletPaul: I have added a few records to your SQL Fiddle, if you don't mind
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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

good point about duplicates

Paul

{+edit} but if changing the sqlfiddle 'schema' the url changes :(
0
 
PaulCommented:
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

Open in new window

 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

Open in new window

0
 
d1cjm1exAuthor Commented:
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
 
d1cjm1exAuthor Commented:
Both answers work given the situation with potential for bad data.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now