Grouping problem - or is this possible at all?
-
I have a table with contains parent-child relationship.
Employee table
EmpID int Primary, Identity
EmpName varchar
ReportTo int References(EmpID) of Employee table
Top level EmpID does not have a parent and all other employees must have a reportto.
I want to get all the people reporting to the top level and top 4 employees under each of these categories.
For example: (if the table contains these values)
1 a null
2 bb a
3 cc a
4 bba bb
5 bbb bb
6 bbc bb
7 abc bba
I want to get records from 2 and its first four employees and then record 3 and its four employees and so on
Any help is greatly appreciated.
-
I'm not functioning too well at this late hour...remi will be along soon to do the rest of this...
create table Employee
(EmpID int,
EmpName VarChar(50),
ReportTo int)
Insert Into Employee (EmpID, EmpName) Values(1,'a')
Insert Into Employee values(2, 'bb', 1)
Insert Into Employee values(3, 'cc', 1)
Insert Into Employee values(4, 'bba', 2)
Insert Into Employee values(5, 'bbb', 2)
Insert Into Employee values(6, 'bbc', 2)
Insert Into Employee values(7, 'abc', 4)
Insert Into Employee values(8, 'abb', 4)
Insert Into Employee values(9, 'aab', 4)
Insert Into Employee values(10, 'aa', 3)
select a.empid, a.empname as manager, b.empname as employee
into #TEST
from employee a
inner join
employee b
on
a.empid = b.reportto
group by a.empname, a.empid, b.empname
order by a.empid
**ASCII stupid question, get a stupid ANSI !!!** -
okay Anjali - I think this should do it...I tested with top 2 (since I'd inserted a limited # of rows)....
select a.empid, a.empname as manager, b.empname as employee
from employee a
inner join
employee b
on
a.empid = b.reportto
and b.empname in (select top 2 c.empname from employee c where a.empid = c.reportto group by c.empname)
group by a.empname, a.empid, b.empname
order by a.empid
**ASCII stupid question, get a stupid ANSI !!!** -
Try this:
SELECT *
FROM (select E1.EmpId, E1.EmpName, E2.ReportTo from Employee E1
inner join Employee E2 on E1.ReportTo = E2.EmpID
where E2.ReportTo IS NULL) TL
INNER JOIN Employee E on E.ReportTo = TL.EmpId
WHERE (SELECT COUNT(EmpId)
From Employee E4
WHERE E4.ReportTo = E.ReportTo
and E4.EmpId <=E.EmpId) < 5
If I did not embedded too many bugs
it must work.TL is nested query returning Top Level employees, E contains employees who reports to top level.
_____________
Code for TallyGenerator -
And "E4.EmpId <=E.EmpId" is priority for employees to be returned.
If you want another criteria choose another unique key for employees - join date, duration period on the job, etc.
_____________
Code for TallyGenerator -
Thank you very much Sushila. Your second option works like a charm. I really appreciate your help.
Also thanks a lot to Sergiy - eventhough the query as given didn't work, but I got the logic and had to change a little bit.
-
oh good! glad it worked!
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply