How can I Ascertain the structure for each person from a self referencing table

Question

I have the following tables:

Employees
-------------
ClockNo     int
CostCentre  varchar
Department  int

and

Departments
-------------
DepartmentCode  int
CostCentreCode  varchar
Parent          int

Departments can have other departments as parents meaning there is infinite hierarchy. All departments belong to a cost centre and so will always have a CostCentreCode. If parent = 0 it is a top level department

Employees must have a CostCentre value but may have a Department of 0 meaning they are not in a department

What I want to try and generate is a query that will give the up to four levels of hierarchy. Like this:

EmployeesLevels
-----------------
ClockNo
CostCentre
DeptLevel1
DeptLevel2
DeptLevel3
DeptLevel4

I've managed to get something to display the department structure on it's own, but I can't work out how to link this to the employees without creating duplicate employee rows:

SELECT d1.Description AS lev1, d2.Description as lev2, d3.Description as lev3, d4.Description as lev4
FROM departments AS d1
LEFT JOIN departments AS d2 ON d2.parent = d1.departmentcode
LEFT JOIN departments AS d3 ON d3.parent = d2.departmentcode
LEFT JOIN departments AS d4 ON d4.parent = d3.departmentcode
WHERE d1.parent=0;

---

SQL To create Structure and some sample data:

CREATE TABLE Employees(
ClockNo integer NOT NULL PRIMARY KEY,
CostCentre varchar(20) NOT NULL,
Department integer NOT NULL);

CREATE TABLE Departments(
DepartmentCode integer NOT NULL PRIMARY KEY,
CostCentreCode varchar(20) NOT NULL,
Parent integer NOT NULL
);

CREATE INDEX idx0 ON Employees (ClockNo);
CREATE INDEX idx1 ON Employees (CostCentre, ClockNo);
CREATE INDEX idx2 ON Employees (CostCentre);

CREATE INDEX idx0 ON Departments (DepartmentCode);
CREATE INDEX idx1 ON Departments (CostCentreCode, DepartmentCode);

INSERT INTO Employees VALUES (1, 'AAA', 0);
INSERT INTO Employees VALUES (2, 'AAA', 3);
INSERT INTO Employees VALUES (3, 'BBB', 0);
INSERT INTO Employees VALUES (4, 'BBB', 4);
INSERT INTO Employees VALUES (5, 'CCC', 0); 
INSERT INTO Employees VALUES (6, 'AAA', 1);
INSERT INTO Employees VALUES (7, 'AAA', 5);
INSERT INTO Employees VALUES (8, 'AAA', 15);

INSERT INTO Departments VALUES (1, 'AAA', 0);
INSERT INTO Departments VALUES (2, 'AAA', 1);
INSERT INTO Departments VALUES (3, 'AAA', 1);
INSERT INTO Departments VALUES (4, 'BBB', 0);
INSERT INTO Departments VALUES (5, 'AAA', 3);
INSERT INTO Departments VALUES (12, 'AAA', 5);
INSERT INTO Departments VALUES (15, 'AAA', 12);

This gives the following structure (employee clock numbers in square brackets):

Root
  |
  |---AAA                   [1]
  |    \---1                [6]
  |       |---2     
  |       \---3             [2]
  |          \---5          [7]
  |             \---12
  |                \---15   [8]
  |
  |---BBB                   [3]
  |    \---4                [4]
  |
  \---CCC                   [5]

The query should return the following:

ClockNo CostCentre Level1 Level2 Level3 Level4
1       AAA        
2       AAA        1      3
3       BBB
4       BBB        4
5       CCC
6       AAA        1
7       AAA        1      3       5
8       AAA        1      3       5      12  *

* In the case of Employee 8, they are in level5. Ideally I would like to show all their levels down to level4, but I am happy just to show the CostCentre in this case

Cassaundra

Do you mean that the employee entity is fully defined and you want to get all CostCentre for his/her department and the parents departments? – Puling

Yes employee if always defined. I basically want employees left outer joined onto the department structure – Cassaundra

"So if an employee is in CostCentreA and DepartmentC, DepartmentC has a parent of DepartmentB, both DepartmentB and DepartmentC are in CostCentreA this is what the query should return", what if DepB and DepC are NOT under CostCentreA, can we return it too? – Jester

Do you always want 4 levels of hierarchy ? IE should it be adaptable further to get more levels (dynamic query), and/or if an employee is on level 3 and thus don't have a 4th level, do you want to only display 3 levels or do you want the 4th level column with no data ? – Samul

@Jester - departments are always in a cost centre – Cassaundra

@ThomasG - I would always like four levels, so there would be nulls in the level4 column in my last example – Cassaundra

@MattWilko I am not familiar with Pervasive syntax, but is ´with´ common table expression supported? – Earthen

@Earthen - I can't find any reference to it in the docs no – Cassaundra

Answer 1

SunnyMagadan's query is good. But depending on number of employees in a department you may wish to try the following one which leaves DB optimizer an opportunity to traverse department hierarchy only once for a department instead of repeating it for every employee in a department.

SELECT e.ClockNo, e.CostCentre,  Level1, Level2, Level3, Level4
FROM Employees e
LEFT JOIN 
    (SELECT 
         d1.departmentcode
        , d1.CostCentreCode
        , coalesce (d4.departmentcode, d3.departmentcode
                    , d2.departmentcode, d1.departmentcode) AS Level1
        , case when d4.departmentcode is not null then d3.departmentcode        
               when d3.departmentcode is not null then d2.departmentcode
               when d2.departmentcode is not null then d1.departmentcode end as Level2
        , case when d4.departmentcode is not null then d2.departmentcode
               when d3.departmentcode is not null then d1.departmentcode end as Level3
        , case when d4.departmentcode is not null then d1.departmentcode end as Level4
    FROM departments AS d1
    LEFT JOIN departments AS d2 ON d1.parent = d2.departmentcode
    LEFT JOIN departments AS d3 ON d2.parent = d3.departmentcode
    LEFT JOIN departments AS d4 ON d3.parent = d4.departmentcode) d
ON d.DepartmentCode = e.Department AND d.CostCentreCode = e.CostCentre
;

EDIT Regarding level 5+ departments.

Any fixed step query can not get top 4 levels for them. So change above query just to mark them some way, -1 for example.

, case when d4.Parent > 0 then NULL else 
    coalesce (d4.departmentcode, d3.departmentcode
            , d2.departmentcode, d1.departmentcode) end AS Level1

and so on.

Fern

Have just noticed that this gives the lowest four levels for an employee who it is a level > 4, rather than the top 4 levels which is the desired result – Cassaundra

Exactly. It goes from bottom to top. Do you mean both departments below level 4 and their employees never count? Then subquery should have 5th level and WHERE level5 is null. – Fern

No they do count, but is there a way to change this such that it goes from top to bottom stopping at four levels? I have modified by question to give the desired data and results – Cassaundra

Indefinite number of levels... To display any higher part of the hierarchy path we need traverse to it from buttom or fully explore it from top. Prevasive has no recursion AFAIK, no CONNECT BY, no RCTE. I'm afraid you should go procedural way. Or leave the levels to your sample employee 8 null. – Fern

Thanks - the last part has sorted out the issues for me - many thanks – Cassaundra

Answer 2

When we join the tables we should stop further traversal of the path when we found proper department that belongs to the Employee at previous level.

Also we have exceptional case when Employee.Department=0. In this case we shouldn't join any of departments, because in this case Department is the Root.

We need to choose only those records which contains employee's Department at one of the levels. In case if employee's department level is greater than 4 we should expand all 4 levels of departments and show them as is (even if can't reach the desired department level and didn't find it within expanded ones).

select e.ClockNo, 
       e.CostCentre, 
       d1.DepartmentCode as Level1, 
       d2.DepartmentCode as Level2, 
       d3.DepartmentCode as Level3, 
       d4.DepartmentCode as Level4
from Employees e
left join Departments d1 
          on e.CostCentre=d1.CostCentreCode 
          and d1.Parent=0 
          and ((d1.DepartmentCode = 0 and e.Department = 0) or e.Department <> 0)
left join Departments d2 
          on d2.parent=d1.DepartmentCode 
          and (d1.DepartMentCode != e.Department and e.Department<>0)
left join Departments d3 
          on d3.parent=d2.DepartmentCode 
          and (d2.DepartMentCode != e.Department and e.Department<>0)
left join Departments d4 
          on d4.parent=d3.DepartmentCode 
          and (d3.DepartMentCode != e.Department and e.Department<>0)
where e.Department=d1.DepartmentCode 
      or e.Department=d2.DepartmentCode 
      or e.Department=d3.DepartmentCode 
      or e.Department=d4.DepartmentCode 
      or e.Department=0
      or (
        (d1.DepartmentCode is not null) and
        (d2.DepartmentCode is not null) and
        (d3.DepartmentCode is not null) and
        (d4.DepartmentCode is not null)
      )
order by e.ClockNo;

Frequent

This is a great succinct solution. It runs very fast on my test machine ~20ms. – Cassaundra

Actually there is a small issue with this as it doesn't include employees at all who are in a lower level that 4 – Cassaundra

Sorry, didn't get the point. According to the source data all employees are in lower level than 4 and current query returns them. Could you provide an example, please? – Frequent

sorry yes - my example didn't include this. I have now added these extra levels and desired output into the question – Cassaundra

Thank you for example. It's not hard to fix. I'll update my answer. – Frequent

Your edit creates duplicates for anyone in levels lower than 4 – Cassaundra

Yes. See it. When we don't expand full path to the department then it's not possible to achieve this. With the current approach we look up only on 4 levels into deep and it's not possible to predict which of these subpathes will follow us to the searched department. That's why you see these duplications. I think that with the current database model it's not possible to achieve correct result. Maybe it's better to store a tree in a different way which will be more flexible and suitable to your needs. I can revert my query back to the original version which doesn't show duplications. – Frequent

Answer 3

The main challenge here is that the employee's department might need to be displayed in column Level1, Level2, Level3, or Level4, depending on how many upper levels there are for that department in the hierarchy.

I would suggest to first query the number of department levels there are for each employee in an inner query, and then to use that information to put the department codes in the right column:

SELECT    ClockNo, CostCentre,
          CASE LevelCount
             WHEN 1 THEN Dep1
             WHEN 2 THEN Dep2
             WHEN 3 THEN Dep3
             ELSE        Dep4
          END Level1,
          CASE LevelCount
             WHEN 2 THEN Dep1
             WHEN 3 THEN Dep2
             WHEN 4 THEN Dep3
          END Level2,
          CASE LevelCount
             WHEN 3 THEN Dep1
             WHEN 4 THEN Dep2
          END Level3,
          CASE LevelCount
             WHEN 4 THEN Dep1
          END Level4
FROM      (SELECT   e.ClockNo, e.CostCentre, 
                    CASE WHEN d2.DepartmentCode IS NULL THEN 1
                      ELSE CASE WHEN d3.DepartmentCode IS NULL THEN 2
                        ELSE CASE WHEN d4.DepartmentCode IS NULL THEN 3
                           ELSE 4
                        END
                      END
                    END AS LevelCount,
                    d1.DepartmentCode Dep1, d2.DepartmentCode Dep2,
                    d3.DepartmentCode Dep3, d4.DepartmentCode Dep4
          FROM      Employees e
          LEFT JOIN departments AS d1 ON d1.DepartmentCode = e.Department
          LEFT JOIN departments AS d2 ON d2.DepartmentCode = d1.Parent
          LEFT JOIN departments AS d3 ON d3.DepartmentCode = d2.Parent
          LEFT JOIN departments AS d4 ON d4.DepartmentCode = d3.Parent) AS Base
ORDER BY  ClockNo

SQL Fiddle

Alternatively, you could do a plain UNION ALL of the 5 possible scenarios in terms of existing levels (chains of 0 to 4 departments):

SELECT     ClockNo, CostCentre,       d4.DepartmentCode Level1,
           d3.DepartmentCode Level2,  d2.DepartmentCode Level3,
           d1.DepartmentCode Level4
FROM       Employees e
INNER JOIN departments AS d1 ON d1.DepartmentCode = e.Department
INNER JOIN departments AS d2 ON d2.DepartmentCode = d1.Parent
INNER JOIN departments AS d3 ON d3.DepartmentCode = d2.Parent
INNER JOIN departments AS d4 ON d4.DepartmentCode = d3.Parent
UNION ALL
SELECT     ClockNo, CostCentre, d3.DepartmentCode,
           d2.DepartmentCode,   d1.DepartmentCode, NULL
FROM       Employees e
INNER JOIN departments AS d1 ON d1.DepartmentCode = e.Department
INNER JOIN departments AS d2 ON d2.DepartmentCode = d1.Parent
INNER JOIN departments AS d3 ON d3.DepartmentCode = d2.Parent
WHERE      d3.Parent = 0
UNION ALL
SELECT     ClockNo, CostCentre, d2.DepartmentCode,
           d1.DepartmentCode,   NULL, NULL
FROM       Employees e
INNER JOIN departments AS d1 ON d1.DepartmentCode = e.Department
INNER JOIN departments AS d2 ON d2.DepartmentCode = d1.Parent
WHERE      d2.Parent = 0
UNION ALL
SELECT     ClockNo, CostCentre, d1.DepartmentCode Level1,
           NULL, NULL, NULL
FROM       Employees e
INNER JOIN departments AS d1 ON d1.DepartmentCode = e.Department
WHERE      d1.Parent = 0
UNION ALL
SELECT     ClockNo, CostCentre, NULL, NULL, NULL, NULL
FROM       Employees e
WHERE      e.Department = 0
ORDER BY   ClockNo

SQL Fiddle

Stoss

Thanks for the effort. Unfortunately this query takes so long to run it times out. – Cassaundra

Could you provide in your question which indexes you have on the tables? – Stoss

I have added primary key and index definitions to the sql. Running this in sqlfiddle times out as well, even with the added indexes – Cassaundra

Could you add the primary key and index creation statements in the create statements you already provided in the question? I think it would benefit everyone working on this question. – Stoss

I did not have a time-out in sqlfiddle when it was not overloaded with requests, because that is an issue with sqlfiddle: it cannot cope with all the traffic it gets. – Stoss

Answer 4

SELECT  [ClockNo]
    ,   [CostCentre]    
    ,   CASE
            WHEN Department <> 0 THEN dept.[Level1]         
        END AS [Level1]
    ,   CASE
            WHEN Department <> 0 THEN dept.[Level2]         
        END AS [Level2]
    ,   CASE
            WHEN Department <> 0 THEN dept.[Level3]         
        END AS [Level3]
    ,   CASE
            WHEN Department <> 0 THEN dept.[Level4]         
        END AS [Level4]

FROM    [Employees] emp
LEFT JOIN
(
SELECT  
        CASE 
            WHEN d4.[DepartmentCode] IS NOT NULL THEN d4.[DepartmentCode]
            WHEN d3.[DepartmentCode] IS NOT NULL THEN d3.[DepartmentCode]
            WHEN d2.[DepartmentCode] IS NOT NULL THEN d2.[DepartmentCode]
            ELSE d1.[DepartmentCode]
        END     AS  [Level1]
    ,   CASE 
            WHEN d4.[DepartmentCode] IS NOT NULL THEN d3.[DepartmentCode]
            WHEN d3.[DepartmentCode] IS NOT NULL THEN d2.[DepartmentCode]
            WHEN d2.[DepartmentCode] IS NOT NULL THEN d1.[DepartmentCode]
            ELSE NULL
        END     AS  [Level2]
    ,   CASE 
            WHEN d4.[DepartmentCode] IS NOT NULL THEN d2.[DepartmentCode]
            WHEN d3.[DepartmentCode] IS NOT NULL THEN d1.[DepartmentCode]           
            ELSE NULL
        END     AS  [Level3]
    ,   CASE 
            WHEN d4.[DepartmentCode] IS NOT NULL THEN d1.[DepartmentCode]           
            ELSE NULL
        END     AS  [Level4]
    ,   d1.[DepartmentCode] AS  [DepartmentCode]    
    ,   d1.[CostCentreCode] AS  [CostCenter]
FROM    [Departments] d1
LEFT JOIN
        [Departments] d2
ON      d1.[Parent] = d2.[DepartmentCode]
LEFT JOIN
        [Departments] d3
ON      d2.[Parent] = d3.[DepartmentCode]
LEFT JOIN
        [Departments] d4
ON      d3.[Parent] = d4.[DepartmentCode]
) AS dept
ON  emp.[Department] = dept.[DepartmentCode]
ORDER BY emp.[ClockNo]

Swedenborgian

This works fine and runs quite fast ~22ms on my test machine, but I have accepted @sunnymagadan as the answer as his query was more succinct and runs slightly faster – Cassaundra

Answer 5

Try this query. Not sure how it will show itself performance-wise on large data with this COALESCE in place.

The idea is to build a derived table of hierarchies leading to each Department

lev1    lev2    lev3    lev4
1       NULL    NULL    NULL
1       2       NULL    NULL
1       3       NULL    NULL
1       3       5       NULL
4       NULL    NULL    NULL

and then use rightmost department to join it with Employees. Here's the full query:

    SELECT
    ClockNo,
    CostCentre,
    lev1,
    lev2,
    lev3,
    lev4
FROM Employees
LEFT JOIN
(
    SELECT
    d1.DepartmentCode AS lev1,
    NULL as lev2,
    NULL as lev3,
    NULL as lev4
    FROM departments AS d1
    WHERE d1.parent=0
    UNION ALL
    SELECT
        d1.DepartmentCode AS lev1,
        d2.DepartmentCode as lev2,
        NULL as lev3,
        NULL as lev4
    FROM departments AS d1
        JOIN departments AS d2 ON d2.parent = d1.departmentcode
    WHERE d1.parent=0
    UNION ALL
    SELECT
        d1.DepartmentCode AS lev1,
        d2.DepartmentCode as lev2,
        d3.DepartmentCode as lev3,
        NULL as lev4
    FROM departments AS d1
        JOIN departments AS d2 ON d2.parent = d1.departmentcode
        JOIN departments AS d3 ON d3.parent = d2.departmentcode
    WHERE d1.parent=0
    UNION ALL
    SELECT
        d1.DepartmentCode AS lev1,
        d2.DepartmentCode as lev2,
        d3.DepartmentCode as lev3,
        d4.DepartmentCode as lev4
    FROM departments AS d1
        JOIN departments AS d2 ON d2.parent = d1.departmentcode
        JOIN departments AS d3 ON d3.parent = d2.departmentcode
        JOIN departments AS d4 ON d4.parent = d3.departmentcode
    WHERE d1.parent=0
) Department
    ON COALESCE(Department.lev4, Department.lev3, Department.lev2, Department.lev1) = Employees.Department
ORDER BY ClockNo

Expulsion

This runs ok on my test machine ~40ms but I have accepted another answer which runs quicker and is more succinct. Thanks for the help – Cassaundra

Answer 6

I would suggest that you seperate the query for getting the employee and getting his / her department hierarchy.

To get the hierarchy of the department, I would suggest you use recursive CTE something like this:

with DepartmentList (DepartmentCode, CostCentreCode, Parent) AS
(
    SELECT 
        parentDepartment.DepartmentCode, 
        parentDepartment.CostCentreCode, 
        parentDepartment.Parent
    FROM Departments parentDepartment
    WHERE DepartmentCode = @departmentCode

    UNION ALL

    SELECT 
        childDepartment.DepartmentCode
        childDepartment.CostCentreCode,
        childDepartment.Parent,
    FROM Departments childDepartment
    JOIN DepartmentList
    ON childDepartment.Parent = DepartmentList.DepartmentCode
)

SELECT * FROM DepartmentList

This is not the direct answer to your question, but this will give you option and idea. Hope this helps.

Jester

Answer 7

So I've taken two steps in order to get this done:

1. I had to generate levels for deparments recursively
2. Generate all possible parent nodes so that I could display them in pivoted view

This recursive query builds DepartmentLevels:

;WITH CTE (DepartmentCode, CostCentreCode, Parent, DepartmentLevel)
AS (
    SELECT D.DepartmentCode, D.CostCentreCode, D.Parent, 1
    FROM dbo.Departments AS D
    WHERE D.Parent = 0
    UNION ALL
    SELECT D.DepartmentCode, D.CostCentreCode, D.Parent, C.DepartmentLevel + 1
    FROM dbo.Departments AS D
    INNER JOIN CTE AS C
        ON C.DepartmentCode = D.Parent
        AND C.CostCentreCode = D.CostCentreCode
    )
SELECT *
INTO #DepartmentLevels
FROM CTE;

That's the output:

╔════════════════╦════════════════╦════════╦═════════════════╗
║ DepartmentCode ║ CostCentreCode ║ Parent ║ DepartmentLevel ║
╠════════════════╬════════════════╬════════╬═════════════════╣
║              1 ║ AAA            ║      0 ║               1 ║
║              4 ║ BBB            ║      0 ║               1 ║
║              2 ║ AAA            ║      1 ║               2 ║
║              3 ║ AAA            ║      1 ║               2 ║
║              5 ║ AAA            ║      3 ║               3 ║
╚════════════════╩════════════════╩════════╩═════════════════╝

Now this query will generate all possible parent nodes for each node (a kind of a mapping table):

;WITH CTE (DepartmentCode, CostCentreCode, Parent, DepartmentLevelCode)
AS (
    SELECT D.DepartmentCode, D.CostCentreCode, D.Parent, D.DepartmentCode
    FROM dbo.Departments AS D
    UNION ALL
    SELECT D.DepartmentCode, D.CostCentreCode, D.Parent, C.DepartmentLevelCode
    FROM dbo.Departments AS D
    INNER JOIN CTE AS C
        ON C.Parent = D.DepartmentCode
    )
SELECT *
FROM CTE;

Which gives us this result:

╔════════════════╦════════════════╦════════╦═════════════════════╗
║ DepartmentCode ║ CostCentreCode ║ Parent ║ DepartmentLevelCode ║
╠════════════════╬════════════════╬════════╬═════════════════════╣
║              1 ║ AAA            ║      0 ║                   1 ║
║              2 ║ AAA            ║      1 ║                   2 ║
║              3 ║ AAA            ║      1 ║                   3 ║
║              4 ║ BBB            ║      0 ║                   4 ║
║              5 ║ AAA            ║      3 ║                   5 ║
║              3 ║ AAA            ║      1 ║                   5 ║
║              1 ║ AAA            ║      0 ║                   5 ║
║              1 ║ AAA            ║      0 ║                   3 ║
║              1 ║ AAA            ║      0 ║                   2 ║
╚════════════════╩════════════════╩════════╩═════════════════════╝

Now we can combine these three buddies together with Employees table and get desired output:

;WITH CTE (DepartmentCode, CostCentreCode, Parent, DepartmentLevelCode)
AS (
    SELECT D.DepartmentCode, D.CostCentreCode, D.Parent, D.DepartmentCode
    FROM dbo.Departments AS D
    UNION ALL
    SELECT D.DepartmentCode, D.CostCentreCode, D.Parent, C.DepartmentLevelCode
    FROM dbo.Departments AS D
    INNER JOIN CTE AS C
        ON C.Parent = D.DepartmentCode
    )
SELECT E.ClockNo
    , E.CostCentre
    , C.Level1
    , C.Level2
    , C.Level3
    , C.Level4
FROM dbo.Employees AS E
OUTER APPLY (
    SELECT MAX(CASE WHEN DL.DepartmentLevel = 1 THEN C.DepartmentCode END)
        , MAX(CASE WHEN DL.DepartmentLevel = 2 THEN C.DepartmentCode END)
        , MAX(CASE WHEN DL.DepartmentLevel = 3 THEN C.DepartmentCode END)
        , MAX(CASE WHEN DL.DepartmentLevel = 4 THEN C.DepartmentCode END)
    FROM CTE AS C
    INNER JOIN #DepartmentLevels AS DL
        ON DL.DepartmentCode = C.DepartmentCode
    WHERE C.DepartmentLevelCode = E.Department
    ) AS C(Level1, Level2, Level3, Level4);

It will give this:

╔═════════╦════════════╦════════╦════════╦════════╦════════╗
║ ClockNo ║ CostCentre ║ Level1 ║ Level2 ║ Level3 ║ Level4 ║
╠═════════╬════════════╬════════╬════════╬════════╬════════╣
║       1 ║ AAA        ║        ║        ║        ║        ║
║       2 ║ AAA        ║ 1      ║ 3      ║        ║        ║
║       3 ║ BBB        ║        ║        ║        ║        ║
║       4 ║ BBB        ║ 4      ║        ║        ║        ║
║       5 ║ CCC        ║        ║        ║        ║        ║
║       6 ║ AAA        ║ 1      ║        ║        ║        ║
║       7 ║ AAA        ║ 1      ║ 3      ║ 5      ║        ║
╚═════════╩════════════╩════════╩════════╩════════╩════════╝

This query will find coresponding DepartmentLevelCode based on DepartmentCode and will pivot stuff based on the DepartmentLevel. Hopefully it's right.

Hermann

Thanks for the help and the effort. Unfortunately my db does not support CTEs, but this might help someone else – Cassaundra

@MattWilko Next time it would be a wildly good idea to specify actual RDBMS product you're using. :) – Hermann

If you check the tags you will see 'Pervasive-sql' - that is the actual RDBMS I am using ;-) – Cassaundra