October 13, 2009 at 2:40 pm
I need some help with a SQL problem.
The actual DDL and DATA are proprietary, so I have created a similar example.
I have two tables. One of Departments and one of Employees.
A department can contain a sub-departments, as well as employees.
There is a sequence on both tables. The sequence is defined as the sequence of this entity within its parent.
How can I get a list of employees in the correct order?
Sample DDL & Data:
DECLARE @Department TABLE
(
DepartmentIDINT
,NameVARCHAR(50)
,ParentDepartmentIDINT
,SequenceINT
)
DECLARE @Employee TABLE
(
EmployeeIDINT IDENTITY(1,1)
,NameVARCHAR(50)
,DepartmentIDINT
,SequenceINT
)
INSERT INTO @Department(DepartmentID, Name, ParentDepartmentID, Sequence)
SELECT 1, 'ABC Company', NULL, 1
UNION ALL SELECT 2, 'Accounting', 1, 2
UNION ALL SELECT 3, 'Development', 1, 3
UNION ALL SELECT 4, 'Web', 3, 3
UNION ALL SELECT 5, 'Database', 3, 4
SELECT *
FROM @Department
INSERT INTO @Employee(Name, DepartmentID, Sequence)
SELECT 'John', 1, 1
UNION ALL SELECT 'Mike', 2, 1
UNION ALL SELECT 'Bob', 2, 2
UNION ALL SELECT 'Shelly', 3, 1
UNION ALL SELECT 'Glen', 3, 2
UNION ALL SELECT 'James', 4, 1
UNION ALL SELECT 'Kevin', 3, 5
SELECT *
FROM @Employee
Expected Results:
/*
DepartmentIDDepartmentNameDepartmentSeqEmployeeIDEmployeeNameEmployeeSeq
1ABC Company11John1
2Accounting22Mike1
2Accounting23Bob2
3Development34Shelly1
3Development35Glen2
4Web36James 1
3Development37Kevin5
*/
October 13, 2009 at 2:44 pm
That seems like a fairly trivial issue for a recursive CTE to handle. It also looks like homework, though that could just be a side-effect of you annonymizing the data.
Are you familiar with Common Table Expressions?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 13, 2009 at 3:00 pm
This is NOT a homework assignment.
It is a multi-million dollar project on an extremely tight deadline.:w00t:
I have written many recursive CTEs in the past.
The reason I am having difficulty with this one is that the data is contained in two tables, not one.
Any help would be much appreciated.
October 13, 2009 at 3:01 pm
And yes, the real data has nothing to do with employees or departments.
It is just an analogy since I can not post the real data.
October 14, 2009 at 7:31 am
Why does James come before Kevin in the desired output? Why split that department (Development) in two and interject Web in the middle?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 14, 2009 at 7:50 am
Basically the key thing here is the sequencing.
Every child node, whether it is a department or employee, is given a sequence within its parent .
An employee can come before or after a department.
The example is a little weird because no one would really care the exact order of employees.
However, sequence is critical in the real data I'm working with.
Visually the structure would look like this: (sequence numbers are in parenthesis)
$ ABC Company (1)
* John (1)
$ Accounting (2)
* Mike (1)
* Bob (2)
$ Development (3)
* Shelly (1)
* Glen (2)
$ Web (3)
* James
$ Database (4)
* Kevin (5)
October 14, 2009 at 8:06 am
You'll need to modify this a bit to get exactly what you want for columns, but it does the job on the sequence and should be easy enough to make into what you need for your actual tables.
;with
Entities as
(select DepartmentID, Name, ParentDepartmentID as ParentID, right('000' + cast(sequence as varchar(3)), 3) as Seq
from @department
union all
select null, Name, DepartmentID, right('000' + cast(sequence as varchar(3)), 3) as Seq
from @employee),
Hierarchy as
(select DepartmentID, Name, ParentID, cast(Seq as varchar(100)) as Seq
from Entities
where ParentID is null
union all
select E2.DepartmentID, E2.Name, E2.ParentID, cast(H.Seq + E2.Seq as varchar(100))
from Entities E2
inner join Hierarchy H
on E2.ParentID = H.DepartmentID)
select *
from Hierarchy H1
inner join Hierarchy H2
on H1.DepartmentID = H2.ParentID
where H1.DepartmentID is not null
and H2.DepartmentID is null
order by H2.Seq;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 14, 2009 at 8:46 am
Very cool! This looks really awesome -- I'm still playing with integrating it into my code.
Thanks so much for your help 🙂
October 15, 2009 at 11:50 am
This was absolutely perfect!
Thanks for the help. You really saved my project!
October 15, 2009 at 11:54 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply