From: erik.lennart.jonsson@gmail.com   
      
   On 2010-08-28 11:03, Erland Sommarskog wrote:   
   > Michel Esber (michel@automatos.com) writes:   
   >> from T_RECURS R inner join DEPARTMENT D on (R.DEP_ID, R.PARENT) =   
   >> (D.DEP_ID, D.PARENT)   
   >   
   > Wait! That is not legal syntax for SQL Server! Are you on Oracle?   
   >   
   >> What am I actually looking for is also the count of Persons that are   
   >> in all sub-departments in that hierarchy, including the departement a   
   >> person is currently in. Somehow I can't get this to work.   
   >   
   > Although it looks like a standard problem it is not exactly trivial, if   
   > you have not done it before. I know. I had to give up my first attempt   
   > the other day, and I did not come back to the problem until now.   
   >   
   > I think the query you have is a non-starter. You start from the bottom   
   > and work upwards. This means that you start on every node, so you get   
   > lots of extra rows. This can easily be dealt with a NOT EXISTS in the   
   > anchor, but as you accumulate when you traverse upwards there is another   
   > issue which your sample data would not reveal: If department 4 has one guy,   
   > and two subdepartments 5 and 6 with one two guys each, you will count the   
   > guy in dept 4 twice.   
   >   
   > Eventually I came to the conclusion that you cannot do this without a   
   > materialised path (unless you switch to a representation with nested   
   > sets). Traverse the tree from the top, to get the path for all nodes,   
   > then you can join the tree with itself with a LIKE expression to get   
   > all the subdepartments:   
   >   
   >    WITH depcnt (DEP_ID, cnt) AS (   
   >       SELECT DEP_ID, COUNT(*)   
   >       FROM   PERSON   
   >       GROUP  BY DEP_ID   
   >    ), recurs (DEP_ID, iter, path) AS (   
   >       SELECT DEP_ID, 0, cast(str(DEP_ID, 4) as varchar(8000))   
   >       FROM   DEPARTMENT   
   >       WHERE  PARENT = -2   
   >       UNION ALL   
   >       SELECT D.DEP_ID, r.iter+1, r.path + str(D.DEP_ID, 4)   
   >       FROM   recurs r   
   >       JOIN   DEPARTMENT D ON r.DEP_ID = D.PARENT   
   >    )   
   >    SELECT R1.DEP_ID, SUM(dc.cnt)   
   >    FROM   recurs R1   
   >    JOIN   recurs R2 ON R2.path LIKE R1.path + '%'   
   >    LEFT   JOIN depcnt dc ON R2.DEP_ID = dc.DEP_ID   
   >    GROUP  BY R1.DEP_ID   
   >    ORDER BY R1.DEP_ID   
   >   
      
   Erland, can you post the output of your query? As you point out there   
   are some mssql specific constructs in there and I dont have access to   
   any mssql server, it would be interesting to compare your results with mine.   
      
   Judging from your sample data I assumed that you are Swedish, and some   
   googling strengthens that hypothesis. Jag bor själv i Umeå, men av någon   
   underlig anledning har jag tidigare antagit att du kommer från Tyskland,   
   det är en liten värld vi lever i ;-)   
      
   /Lennart   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)