Hierarchy Wise Report - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Hierarchy Wise Report

Author

Topic

sanjay5219
Posting Yak Master

240 Posts

Posted - 2009-09-29 : 05:45:06

Dear all,

I have one table Emp_Master where i am managing Employee_Code,Team_Lead_Id.

Now i have to create a report where Employee should see their data, tl should see their data and Tl's team leader should see their TL and theor employees.

E.g
Emp-TL
123-345
234-345
264-345
345-678
678-908

In this case 345(TL ) should see the data of 123,234,264.And 678(tl) should see data of 123,234,264 & 345.In same manner 908 should see the data of 123,234,264,345 & 678.

Please tell me how to where condition in this situation

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 05:50:44

if you're using SQL 2005 use recursive CTEs


;With Team_CTE (Emp,TL)
AS
(SELECT Emp,TL
FROM YourTable
WHERE Emp=@Emp
UNION ALL
SELECT t.Emp,t.TL
FROM Team_CTE c
INNER JOIN YourTable t
ON t.TL=c.Emp
)
SELECT * FROM Team_CTE

sanjay5219
Posting Yak Master

240 Posts

Posted - 2009-09-29 : 05:51:16

I am using Sql 2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 06:15:26

then try this:-


declare @t table (parent varchar(20), child varchar(20), lev int, fullpath varchar(1000))
declare @lev int
set @lev = 0

--Get Root node(s)
insert @t (parent, child, lev, fullpath)
select null, p.Emp, @lev, p.parent
from   yourtable p
where  p.Emp =@emp

while @@rowcount > 0
begin
       set @lev = @lev + 1

       --Get all children of current level
       insert @t (parent, child, lev, fullpath)
       select h.TL, h.Emp, @lev, t.fullpath + '.' + h.Emp
       from   @t t
       join   yourtable h on h.TL= t.child and t.lev = @lev-1
       left join @t x on x.parent = h.TL and x.child = h.Emp
       where x.parent is null
end
select * from @t order by fullpath

@emp is employee value passed for which you need to view data

sanjay5219
Posting Yak Master

240 Posts

Posted - 2009-09-29 : 06:19:51

I am getting this error

Server: Msg 137, Level 15, State 2, Line 9
Must declare the variable '@emp'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 06:21:47

quote:
Originally posted by sanjay5219

I am getting this error

Server: Msg 137, Level 15, State 2, Line 9
Must declare the variable '@emp'.

you need to declare that and pass a value for it too

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 06:23:25

as an example,


declare @t table (parent varchar(20), child varchar(20), lev int, fullpath varchar(1000))
declare @lev int,@emp int
select @lev = 0,@emp=678 --( as per sample data)

--Get Root node(s)
insert @t (parent, child, lev, fullpath)
select null, p.Emp, @lev, p.parent
from   yourtable p
where  p.Emp =@emp

while @@rowcount > 0
begin
       set @lev = @lev + 1

       --Get all children of current level
       insert @t (parent, child, lev, fullpath)
       select h.TL, h.Emp, @lev, t.fullpath + '.' + h.Emp
       from   @t t
       join   yourtable h on h.TL= t.child and t.lev = @lev-1
       left join @t x on x.parent = h.TL and x.child = h.Emp
       where x.parent is null
end
select * from @t order by fullpath

sanjay5219
Posting Yak Master

240 Posts

Posted - 2009-09-29 : 06:30:00

is there any chance where i can create one procedure for full table,This is running based on give Employee Code.
i have to use query in asp.net like where parent='" & & ''

sanjay5219
Posting Yak Master

240 Posts

Posted - 2009-09-29 : 06:35:46

It's done,Thanks alot dear

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 06:50:15

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources