Hierarchy - SQL Hierarchical query - Multiple layers only care about one

Questions

I have a table that lists job and work order information. Every job has at least one work order, most jobs have many work orders. Some work orders can have sub-work orders and subs can have subs as well. This gives us multiple layers of hierarchy.

For a report I m building, I want to group all work orders by the second level workorder that they fall under. I m calling this the SectorWO.

I ve written the following query which almost achieves what I m looking for, but it does not include the second level work orders themselves. I ve attempted to build sample data on SQL Fiddle but my query will not run there (it does run on production SQL Server 2012, though). It looks like a problem with querying a temporary table after the statement that filled said table has been terminated?

Anyways here s what I ve built on SQL Fiddle, hopefully it is enough to demonstrate...

The schema:

CREATE TABLE WOMaster (WorkOrder VARCHAR(12), ParentWorkOrder VARCHAR(12), Job VARCHAR(12))
INSERT INTO WOMaster (WorkOrder, ParentWorkOrder, Job)
VALUES (1,NULL,101),(2,1,101),(3,2,101),(4,3,101),(5,4,101),(6,4,101),(7,4,101),(8,4,101),(9,2,101),(10,2,101),
    (11,2,101),(12,1,101),(13,12,101),(14,12,101),(15,12,101),(16,2,101),(17,1,101),(18,17,101),(19,17,101),(20,1,101),
    (21,1,101),(22,21,101),(23,22,101),(24,22,101),(25,24,101),(26,24,101),(27,1,101),(28,27,101),(29,28,101),
    (30,1,101),(31,30,101),(32,31,101),(33,32,101),(34,1,101),(35,34,101),(36,35,101),(37,36,101);

The query:

    DECLARE @pJob VARCHAR(Max)

SET @pJob =  101 

DECLARE @pWorkOrder VARCHAR(6)

SET @pWorkOrder =  1 

DECLARE @WOList1 TABLE (
    Job VARCHAR(12)
    ,WorkOrder VARCHAR(12)
    ,ParentWorkOrder VARCHAR(12)
    )
DECLARE @WOList2 TABLE (
    Job VARCHAR(12)
    ,WorkOrder VARCHAR(12)
    ,SectorWO VARCHAR(12)
    )

INSERT INTO @WOList1 (
    Job
    ,WorkOrder
    ,ParentWorkOrder
    )
SELECT Job
    ,WorkOrder
    ,ParentWorkOrder
FROM WOMaster
WHERE Job = @pJob
    AND ParentWorkorder <> WorkOrder;

WITH cte
AS (
    SELECT WO1.WorkOrder
        ,WO1.ParentWorkOrder
    FROM @WOList1 WO1

    UNION ALL

    SELECT c.WorkOrder
        ,WO1.ParentWorkOrder
    FROM @WOList1 WO1
    INNER JOIN cte c ON c.ParentWorkOrder = WO1.WorkOrder
    )
INSERT INTO @WOList2 (
    Job
    ,WorkOrder
    ,SectorWO
    )
SELECT WO1.Job
    ,C.WorkOrder
    ,C.ParentWorkOrder
FROM CTE C
LEFT JOIN @WOList1 WO1 ON C.WorkOrder = WO1.WorkOrder
WHERE C.ParentWorkOrder IN (
        SELECT WorkOrder
        FROM @WOList1
        WHERE ParentWorkOrder = @pWorkOrder
        )

Expected output:

Job WorkOrder   SectorWO
101 2   2
101 3   2
101 4   2
101 5   2
101 6   2
101 7   2
101 8   2
101 9   2
101 10  2
101 11  2
101 16  2
101 12  12
101 13  12
101 14  12
101 15  12
101 17  17
101 18  17
101 19  17
101 20  20
101 21  21
101 22  21
101 23  21
101 24  21
101 25  21
101 26  21
101 27  27
101 28  27
101 29  27
101 30  30
101 31  30
101 32  30
101 33  30
101 34  34
101 35  34
101 36  34
101 37  34

Answers

You haven t given an example of what you want the output to look like. So here s my best try. I think you may be making this more complicated than necessary.

SELECT subwo.job, subwo.workorder SubWO, secondsubwo.workorder SecondSubWO
FROM WOMaster
LEFT JOIN WOMaster SubWO ON Subwo.parentworkorder = womaster.workorder
LEFT JOIN WOMaster SecondSubWo ON SecondSubWo.parentworkorder = Subwo.workorder
WHERE WOMaster.parentworkorder IS NULL
ORDER BY cast(subwo.workorder AS INT), cast(secondsubWO.workorder AS INT)

Now that you have clarified your output. I think you should use a recursive common table expression to get what you want.

;
with woList as
(select toplevel.workorder, toplevel.job, toplevel.parentworkorder
, 1 as WOLevel
From womaster toplevel
UNION ALL
SELECT wo.workorder, wo.job, wo.parentworkorder
, wolist.wolevel+1 as wolevel
FROM womaster wo
INNER JOIN wolist on wo.parentworkorder=wolist.workorder
WHERE wo.parentworkorder is not null)

select 
WoMaster.job, WoMaster.workorder, sector.workorder SectorWO
 from womaster womaster
 inner join wolist sector on sector.workorder=womaster.parentworkorder
 where wolevel=2
UNION ALL 
Select sector2.job, sector2.workorder, sector2.workorder
FROM wolist sector2
where wolevel=2
 order by cast(womaster.job as int), cast(sector.workorder as int), cast(womaster.workorder as int)

http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/ http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/31300560/sql-hierarchical-query-multiple-layers-only-care-about-one

Related