Solved

tsql programming

Posted on 2013-05-13
7
191 Views
Last Modified: 2013-05-15
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
Comment
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
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 250 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

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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 250 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

Open in new window

http://sqlfiddle.com/#!3/7dd0b/8
0
 
LVL 25

Expert Comment

by:chaau
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.

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 49

Expert Comment

by:PortletPaul
ID: 39163674
@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
 
LVL 49

Expert Comment

by:PortletPaul
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

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
 

Author Comment

by:d1cjm1ex
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

by:d1cjm1ex
ID: 39167601
Both answers work given the situation with potential for bad data.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question