Retrive Value in SQL

Question

1.80/5 (2 votes)

See more:

Hi,

i have to create a SSRS Report to retrieve the value according to this condition

A,B,C,D,E are Five users,

A as Junior developer
B as developer
C as senior developer
D as project manager
E as Sr project manager

A reports to B
B reports to C
C reports to D
D reports to E

then

when A logs in he can only see his records,
When B logs in he can see the records for A,B and not able to see the C,D and E records,
when C logs in he can see the records for A,B,C and not able to see the D and E records,
when D logs in he can see the records for B,C,D and not able to see the A and E records,
and when E logs in he can see the records for C,D,E and not able to see the A,B records

is there Anyway to retrieve the value this way,

table structure

ID |Name| Reporting Manager|
---|----|------------------|-------
1 |A |B |
2 |B |C |
3 |C |D |
4 |D |E |
5 |E |F |
thank you

Posted 25-Oct-13 0:46am

write2varun

Updated 25-Oct-13 2:01am

v3

Add a Solution

Comments

Thanks7872 25-Oct-13 7:29am

I don't think some one will try to understand the question which seems more like puzzle.

Note:

A,B,C,D are not three users, its four.

You,your self not clear while writing the question.

ZurdoDev 25-Oct-13 7:33am

Where are you stuck? Yes, just write a SELECT statement to do what you want.

write2varun 25-Oct-13 7:36am

the user can only see the two level below him, how to write the query for that

ZurdoDev 25-Oct-13 7:46am

Since we have no idea what your table structure looks like we can't tell you. Where are you stuck though? Select * FROM table where userid = @userid or userid IN (SELECT userids THAT user has access to see)

write2varun 25-Oct-13 8:02am

I added the table structure

ZurdoDev 25-Oct-13 8:09am

Google sql recursive functions. That way you can get the whole tree of what they have access to.

Raja Sekhar S 28-Oct-13 7:50am

Check the Updated Solution...

2 solutions

Solution 2

SQL

Declare @Ta table(Code Char(2),Description varchar(20))
Insert @Ta

Select 'A','Junior developer'
union all Select 'B','developer'
union all Select 'C','senior developer'
union all Select 'D','project manager'
union all Select 'E','Sr project manager'

Declare @Code Char(2) = 'E'

--Select 1 From @Ta Where @Code = 'A'


IF (( Select Code From @Ta Where Code = 'A') = @Code)

Begin

Select * From @Ta Where Code = 'A'

End

IF (( Select Code From @Ta Where Code = 'B'  ) = @Code)

Begin

Select * From @Ta Where Code In ('A','B')

End

IF (( Select Code From @Ta Where Code = 'C'  ) = @Code)

Begin

Select * From @Ta Where Code In ('A','B','C')

End

IF (( Select Code From @Ta Where Code = 'D'  ) = @Code)

Begin

Select * From @Ta Where Code In ('B','C','D')

End


IF (( Select Code From @Ta Where Code = 'E'  ) = @Code)

Begin

Select * From @Ta Where Code In ('C','D','E')

End

Posted 25-Oct-13 2:21am

VeeraAnnapureddy

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Raja Sekhar S · Accepted Answer · 2013-10-25T02:01:00

U can use something like this...

SQL

Create Table #Temp
        (
        UserGroup Nvarchar(40)
        )

Insert into #Temp
Values('A'),('B'),('C'),('D'),('E')

;With Cte
as
(Select UserGroup,Row_Number()Over(Order by UserGroup) [Hirearchy] From #Temp)

Select Top(3) c.UserGroup,c.Hirearchy from Cte c
Inner join Cte c1 on c.Hirearchy <=c1.Hirearchy
Where c1.UserGroup='E'
Order by c.Hirearchy Desc

Drop Table #Temp

Output:

SQL

UserGroup   Hirearchy
---------   ---------
    E         5
    D         4
    C         3

-- Edit
Query as per your Updated table Structure...

SQL

Create Table #Temp
		(
		 Id Int,
		 Name Nvarchar(40),
		 ReportingManager Nvarchar(40)
		)

Insert into #Temp
Values(1,'A','B'),(2,'B','C'),(3,'C','D'),(4,'D','E'),(5,'E','F')

Declare @LoginName Nvarchar(40) ='E'

;With cte (Id,Name,ReportingManager,Hierarchy) as
(
Select Id,Name,ReportingManager,1 from #Temp Where Name=@LoginName 
Union all
Select t.Id,t.Name,t.ReportingManager,c.Hierarchy+1 From #Temp t 
Inner join cte c on t.ReportingManager=c.Name
)

Select Id,Name,ReportingManager,Hierarchy 
from cte Where Hierarchy <=3

Drop Table #Temp