CTE Recursive Query

Question

CTE Recursive Query

robertopmorris

SSC Eights!

Points: 806
More actions
February 8, 2017 at 12:51 pm

#401583

I have a table with the following fields:
ParentWO
IssueQty
ChildWO
Production_Qty
Qty_Complete
I want to write a recursive statement to find all Child Work Orders (ChildWO) that were issued to Parent Work Orders (ParentWO) - the table RM_WorkOrdersSL contains all Work Orders where other Work Orders were issued to them.
I have written the following query:
WITH mlBom AS (SELECT ParentWO AS FinishedWO, ParentWO, ChildWO, IssueQty AS ExtendedQty, IssueQty, Production_Qty, Qty_Complete
             FROM   dbo.RM_WorkOrdersSL
             UNION ALL
             SELECT c.FinishedWO, c.ParentWO, n.ChildWO, n.IssueQty AS ExtendedQty, n.IssueQty, n.Production_Qty, n.Qty_Complete
             FROM   dbo.RM_WorkOrdersSL AS n INNER JOIN
                    mlBom AS c ON c.ChildWO = n.ParentWO)
SELECT TOP (100) PERCENT ParentWO, ChildWO, ExtendedQty AS IssueQty, Production_Qty, Qty_Complete
FROM   mlBom AS mlBom_1
GROUP BY ParentWO, ChildWO, ExtendedQty, Production_Qty, Qty_Complete
ORDER BY ParentWO, ChildWO, IssueQty, Production_Qty, Qty_Complete
The original table contains 321 records and the query above returns 405 records - but it takes a long time to execute and sometimes times out.
Is there a more efficient method of writing this CTE query?
I am only using a sample data set so when I try to use the full data set it always times out.
Roberto

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

robertopmorris - Wednesday, February 8, 2017 12:51 PM
I have a table with the following fields:
ParentWO
IssueQty
ChildWO
Production_Qty
Qty_Complete
I want to write a recursive statement to find all Child Work Orders (ChildWO) that were issued to Parent Work Orders (ParentWO) - the table RM_WorkOrdersSL contains all Work Orders where other Work Orders were issued to them.
I have written the following query:
WITH mlBom AS ( SELECT ParentWO AS FinishedWO, ParentWO, ChildWO, IssueQty AS ExtendedQty, IssueQty, Production_Qty, Qty_Complete FROM dbo.RM_WorkOrdersSL UNION ALL SELECT c.FinishedWO, c.ParentWO, n.ChildWO, n.IssueQty AS ExtendedQty, n.IssueQty, n.Production_Qty, n.Qty_Complete FROM dbo.RM_WorkOrdersSL AS n INNER JOIN mlBom AS c ON c.ChildWO = n.ParentWO ) SELECT TOP (100) PERCENT ParentWO, ChildWO, ExtendedQty AS IssueQty, Production_Qty, Qty_Complete FROM mlBom AS mlBom_1 GROUP BY ParentWO, ChildWO, ExtendedQty, Production_Qty, Qty_Complete ORDER BY ParentWO, ChildWO, IssueQty, Production_Qty, Qty_Complete
The original table contains 321 records and the query above returns 405 records - but it takes a long time to execute and sometimes times out.
Is there a more efficient method of writing this CTE query?
I am only using a sample data set so when I try to use the full data set it always times out.
Roberto

Why do you have a GROUP BY without aggregates? I imagine that the TOP(100) PERCENT and ORDER BY is because you're creating a view. Any way, both clauses will be ignored.
Can you post some sample data and DDL (create table and indexes) to understand the problem?

EDIT: Code format.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sue_H SSC Guru Points: 90860 More actions · Answer 2

robertopmorris - Wednesday, February 8, 2017 12:51 PM
I have a table with the following fields:
ParentWO
IssueQty
ChildWO
Production_Qty
Qty_Complete
I want to write a recursive statement to find all Child Work Orders (ChildWO) that were issued to Parent Work Orders (ParentWO) - the table RM_WorkOrdersSL contains all Work Orders where other Work Orders were issued to them.
I have written the following query:
WITH mlBom AS (SELECT ParentWO AS FinishedWO, ParentWO, ChildWO, IssueQty AS ExtendedQty, IssueQty, Production_Qty, Qty_Complete
             FROM   dbo.RM_WorkOrdersSL
             UNION ALL
             SELECT c.FinishedWO, c.ParentWO, n.ChildWO, n.IssueQty AS ExtendedQty, n.IssueQty, n.Production_Qty, n.Qty_Complete
             FROM   dbo.RM_WorkOrdersSL AS n INNER JOIN
                    mlBom AS c ON c.ChildWO = n.ParentWO)
SELECT TOP (100) PERCENT ParentWO, ChildWO, ExtendedQty AS IssueQty, Production_Qty, Qty_Complete
FROM   mlBom AS mlBom_1
GROUP BY ParentWO, ChildWO, ExtendedQty, Production_Qty, Qty_Complete
ORDER BY ParentWO, ChildWO, IssueQty, Production_Qty, Qty_Complete
The original table contains 321 records and the query above returns 405 records - but it takes a long time to execute and sometimes times out.
Is there a more efficient method of writing this CTE query?
I am only using a sample data set so when I try to use the full data set it always times out.
Roberto

It will take quite a bit of work for someone to figure out your tables (one which isn't listed), guess the data types, create sample data, etc.
People get faster replies when they post DDL and sample data. Refer to this to get an idea of what to post to get more replies:
How to post data/code on a forum to get the best help

Sue

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

robertopmorris - Wednesday, February 8, 2017 12:51 PM
I have a table with the following fields:
ParentWO
IssueQty
ChildWO
Production_Qty
Qty_Complete
I want to write a recursive statement to find all Child Work Orders (ChildWO) that were issued to Parent Work Orders (ParentWO) - the table RM_WorkOrdersSL contains all Work Orders where other Work Orders were issued to them.
I have written the following query:
WITH mlBom AS (SELECT ParentWO AS FinishedWO, ParentWO, ChildWO, IssueQty AS ExtendedQty, IssueQty, Production_Qty, Qty_Complete
             FROM   dbo.RM_WorkOrdersSL
             UNION ALL
             SELECT c.FinishedWO, c.ParentWO, n.ChildWO, n.IssueQty AS ExtendedQty, n.IssueQty, n.Production_Qty, n.Qty_Complete
             FROM   dbo.RM_WorkOrdersSL AS n INNER JOIN
                    mlBom AS c ON c.ChildWO = n.ParentWO)
SELECT TOP (100) PERCENT ParentWO, ChildWO, ExtendedQty AS IssueQty, Production_Qty, Qty_Complete
FROM   mlBom AS mlBom_1
GROUP BY ParentWO, ChildWO, ExtendedQty, Production_Qty, Qty_Complete
ORDER BY ParentWO, ChildWO, IssueQty, Production_Qty, Qty_Complete
The original table contains 321 records and the query above returns 405 records - but it takes a long time to execute and sometimes times out.
Is there a more efficient method of writing this CTE query?
I am only using a sample data set so when I try to use the full data set it always times out.
Roberto

Your anchor set is incorrectly defined - you want it to contain top-level parents (rows where the parent is not a child of another row)

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

robertopmorris SSC Eights! Points: 806 More actions · Answer 4

Here is an example data set:

ParentWO 2088/1.1	IssueQty 14	ChildWO 2088/1.1.1	Production_Qty 40	Qty_Complete 40
2088/1.1	25	2088/1.1.1	40	40
2088/1.1	1	2088/1.1.2	40	40
2088/1.1	14	2088/1.1.2	40	40
2088/1.1.1	40	2088/1.1.1.1	40	40
2088/1.1.2	40	2088/1.1.2.1	40	40

I have rewritten the CTE query as follows:
WITH mlBOM AS (SELECT ParentWO AS FinishedWO, ParentWO, ChildWO, IssueQty, Production_Qty, Qty_Complete
             FROM   dbo.RM_WorkOrdersSL
             UNION ALL
             SELECT d.FinishedWO, t.ParentWO, t.ChildWO, d.IssueQty, t.Production_Qty, t.Qty_Complete
             FROM   dbo.RM_WorkOrdersSL AS t INNER JOIN
                     mlBOM AS d ON t.ParentWO = d.ChildWO)
SELECT TOP (100) PERCENT FinishedWO, ParentWO, ChildWO, IssueQty, Production_Qty, Qty_Complete
FROM   mlBOM AS mlBOM1
ORDER BY FinishedWO, ParentWO, ChildWO

This returns the following:

FinishedWO 2088/1.1	ParentWO 2088/1.1	ChildWO 2088/1.1.1	IssueQty 14	Production_Qty 40	Qty_Complete 40
2088/1.1	2088/1.1	2088/1.1.1	25	40	40
2088/1.1	2088/1.1	2088/1.1.2	14	40	40
2088/1.1	2088/1.1	2088/1.1.2	1	40	40
2088/1.1	2088/1.1.1	2088/1.1.1.1	25	40	40
2088/1.1	2088/1.1.1	2088/1.1.1.1	14	40	40
2088/1.1	2088/1.1.2	2088/1.1.2.1	1	40	40
2088/1.1	2088/1.1.2	2088/1.1.2.1	14	40	40
2088/1.1.1	2088/1.1.1	2088/1.1.1.1	40	40	40
2088/1.1.2	2088/1.1.2	2088/1.1.2.1	40	40	40

The last thing I need to do is Group By the ChildWO and sum the ChildWO IssueQty to return the following:

FinishedWO 2088/1.1	ParentWO 2088/1.1	ChildWO 2088/1.1.1	IssueQty 39	Production_Qty 40	Qty_Coomplete 40
2088/1.1	2088/1.1	2088/1.1.2	15	40	40
2088/1.1	2088/1.1.1	2088/1.1.1.1	39	40	40
2088/1.1	2088/1.1.2	2088/1.1.2.1	15	40	40
2088/1.1.1	2088/1.1.1	2088/1.1.1.1	40	40	40
2088/1.1.2	2088/1.1.2	2088/1.1.2.1	40	40	40

Can anyone assist?

Roberto.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

robertopmorris - Thursday, February 9, 2017 1:34 AM
Here is an example data set:
ParentWO
2088/1.1 IssueQty
14 ChildWO
2088/1.1.1 Production_Qty
40 Qty_Complete
40
2088/1.1 25 2088/1.1.1 40 40
2088/1.1 1 2088/1.1.2 40 40
2088/1.1 14 2088/1.1.2 40 40
2088/1.1.1 40 2088/1.1.1.1 40 40
2088/1.1.2 40 2088/1.1.2.1 40 40
I have rewritten the CTE query as follows:
WITH mlBOM AS (SELECT ParentWO AS FinishedWO, ParentWO, ChildWO, IssueQty, Production_Qty, Qty_Complete
             FROM   dbo.RM_WorkOrdersSL
             UNION ALL
             SELECT d.FinishedWO, t.ParentWO, t.ChildWO, d.IssueQty, t.Production_Qty, t.Qty_Complete
             FROM   dbo.RM_WorkOrdersSL AS t INNER JOIN
                     mlBOM AS d ON t.ParentWO = d.ChildWO)
SELECT TOP (100) PERCENT FinishedWO, ParentWO, ChildWO, IssueQty, Production_Qty, Qty_Complete
FROM   mlBOM AS mlBOM1
ORDER BY FinishedWO, ParentWO, ChildWO
This returns the following:
FinishedWO
2088/1.1 ParentWO
2088/1.1 ChildWO
2088/1.1.1 IssueQty
14 Production_Qty
40 Qty_Complete
40
2088/1.1 2088/1.1 2088/1.1.1 25 40 40
2088/1.1 2088/1.1 2088/1.1.2 14 40 40
2088/1.1 2088/1.1 2088/1.1.2 1 40 40
2088/1.1 2088/1.1.1 2088/1.1.1.1 25 40 40
2088/1.1 2088/1.1.1 2088/1.1.1.1 14 40 40
2088/1.1 2088/1.1.2 2088/1.1.2.1 1 40 40
2088/1.1 2088/1.1.2 2088/1.1.2.1 14 40 40
2088/1.1.1 2088/1.1.1 2088/1.1.1.1 40 40 40
2088/1.1.2 2088/1.1.2 2088/1.1.2.1 40 40 40
The last thing I need to do is Group By the ChildWO and sum the ChildWO IssueQty to return the following:
FinishedWO
2088/1.1 ParentWO
2088/1.1 ChildWO
2088/1.1.1 IssueQty
39 Production_Qty
40 Qty_Coomplete
40
2088/1.1 2088/1.1 2088/1.1.2 15 40 40
2088/1.1 2088/1.1.1 2088/1.1.1.1 39 40 40
2088/1.1 2088/1.1.2 2088/1.1.2.1 15 40 40
2088/1.1.1 2088/1.1.1 2088/1.1.1.1 40 40 40
2088/1.1.2 2088/1.1.2 2088/1.1.2.1 40 40 40
Can anyone assist?
Roberto.

Yes... as soon as you read the article that Sue posted a link for so that we can actually load the data instead of having to screenscrape and format. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

robertopmorris - Thursday, February 9, 2017 1:34 AM
Here is an example data set:
ParentWO
2088/1.1 IssueQty
14 ChildWO
2088/1.1.1 Production_Qty
40 Qty_Complete
40
2088/1.1 25 2088/1.1.1 40 40
2088/1.1 1 2088/1.1.2 40 40
2088/1.1 14 2088/1.1.2 40 40
2088/1.1.1 40 2088/1.1.1.1 40 40
2088/1.1.2 40 2088/1.1.2.1 40 40
I have rewritten the CTE query as follows:
WITH mlBOM AS (SELECT ParentWO AS FinishedWO, ParentWO, ChildWO, IssueQty, Production_Qty, Qty_Complete
             FROM   dbo.RM_WorkOrdersSL
             UNION ALL
             SELECT d.FinishedWO, t.ParentWO, t.ChildWO, d.IssueQty, t.Production_Qty, t.Qty_Complete
             FROM   dbo.RM_WorkOrdersSL AS t INNER JOIN
                     mlBOM AS d ON t.ParentWO = d.ChildWO)
SELECT TOP (100) PERCENT FinishedWO, ParentWO, ChildWO, IssueQty, Production_Qty, Qty_Complete
FROM   mlBOM AS mlBOM1
ORDER BY FinishedWO, ParentWO, ChildWO
This returns the following:
FinishedWO
2088/1.1 ParentWO
2088/1.1 ChildWO
2088/1.1.1 IssueQty
14 Production_Qty
40 Qty_Complete
40
2088/1.1 2088/1.1 2088/1.1.1 25 40 40
2088/1.1 2088/1.1 2088/1.1.2 14 40 40
2088/1.1 2088/1.1 2088/1.1.2 1 40 40
2088/1.1 2088/1.1.1 2088/1.1.1.1 25 40 40
2088/1.1 2088/1.1.1 2088/1.1.1.1 14 40 40
2088/1.1 2088/1.1.2 2088/1.1.2.1 1 40 40
2088/1.1 2088/1.1.2 2088/1.1.2.1 14 40 40
2088/1.1.1 2088/1.1.1 2088/1.1.1.1 40 40 40
2088/1.1.2 2088/1.1.2 2088/1.1.2.1 40 40 40
The last thing I need to do is Group By the ChildWO and sum the ChildWO IssueQty to return the following:
FinishedWO
2088/1.1 ParentWO
2088/1.1 ChildWO
2088/1.1.1 IssueQty
39 Production_Qty
40 Qty_Coomplete
40
2088/1.1 2088/1.1 2088/1.1.2 15 40 40
2088/1.1 2088/1.1.1 2088/1.1.1.1 39 40 40
2088/1.1 2088/1.1.2 2088/1.1.2.1 15 40 40
2088/1.1.1 2088/1.1.1 2088/1.1.1.1 40 40 40
2088/1.1.2 2088/1.1.2 2088/1.1.2.1 40 40 40
Can anyone assist?
Roberto.

Run the results of your rCTE directly into a #temporary table. rCTE's don't perform well with aggregates.
Are you absolutely sure your expected results set is correct? You will include dupes in your aggregate. Something doesn't look quite right here. What exactly are you trying to do?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

There are dupes in your sample data set - is this intentional or an oversight?

IF OBJECT_ID('tempdb..#RM_WorkOrdersSL') IS NOT NULL DROP TABLE #RM_WorkOrdersSL;

SELECT *

INTO #RM_WorkOrdersSL

FROM (VALUES

('2088/1.1', 14, '2088/1.1.1', 40, 40),

('2088/1.1', 25, '2088/1.1.1', 40, 40),

('2088/1.1', 1, '2088/1.1.2', 40, 40),

('2088/1.1', 13, '2088/1.1.2', 40, 40),

('2088/1.1.1', 38, '2088/1.1.1.1', 40, 40),

('2088/1.1.2', 40, '2088/1.1.2.1', 40, 40)

) d (ParentWO, IssueQty, ChildWO, Production_Qty, Qty_Complete);

SELECT * FROM #RM_WorkOrdersSL

Removing the dupes and reconfiguring the rCTE to something more conventional yields results which appear more logical - at least, to me:

IF OBJECT_ID('tempdb..#RM_WorkOrdersSL') IS NOT NULL DROP TABLE #RM_WorkOrdersSL;

SELECT *

INTO #RM_WorkOrdersSL

FROM (VALUES

('2088/1.1', 14, '2088/1.1.1', 40, 40),

--('2088/1.1', 25, '2088/1.1.1', 40, 40),

('2088/1.1', 1, '2088/1.1.2', 40, 40),

--('2088/1.1', 13, '2088/1.1.2', 40, 40),

('2088/1.1.1', 38, '2088/1.1.1.1', 40, 40),

('2088/1.1.2', 40, '2088/1.1.2.1', 40, 40)

) d (ParentWO, IssueQty, ChildWO, Production_Qty, Qty_Complete);

WITH mlBOM AS (

SELECT

ParentWO AS FinishedWO,

ParentWO,

ChildWO,

IssueQty,

Production_Qty,

Qty_Complete,

[Level] = CAST(1 AS INT)

FROM #RM_WorkOrdersSL o

WHERE NOT EXISTS (SELECT 1 FROM #RM_WorkOrdersSL i WHERE i.ChildWO = o.ParentWO)

UNION ALL

SELECT

lastIter.FinishedWO,

ThisIter.ParentWO,

ThisIter.ChildWO,

ThisIter.IssueQty,

ThisIter.Production_Qty,

ThisIter.Qty_Complete,

[Level] = lastIter.[Level] + 1

FROM mlBOM lastIter

INNER JOIN #RM_WorkOrdersSL ThisIter

ON ThisIter.ParentWO = lastIter.ChildWO

)

SELECT FinishedWO, ParentWO, ChildWO, IssueQty, Production_Qty, Qty_Complete, [Level]

FROM mlBOM AS mlBOM1

ORDER BY FinishedWO, ParentWO, ChildWO

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

robertopmorris SSC Eights! Points: 806 More actions · Answer 8

I have tried to follow the guide as per the response from Sue and Jeff.

Can someone please advise I have have done this incorrectly so that I can revise.

My SQL code to create a SQLView based on the posted data:
WITH mlBOM AS (SELECT ParentWO AS FinishedWO, ParentWO, PProduction_Qty, PQty_Complete, ChildWO, IssueQty, Production_Qty, Qty_Complete FROM dbo.RM_WorkOrdersSLGroup UNION ALL SELECT d.FinishedWO, t.ParentWO, t.PProduction_Qty, t.PQty_Complete, t.ChildWO, t.IssueQty, t.Production_Qty, t.Qty_Complete FROM dbo.RM_WorkOrdersSLGroup AS t INNER JOIN mlBOM AS d ON t.ParentWO = d.ChildWO) SELECT TOP (100) PERCENT FinishedWO, ParentWO, PProduction_Qty, PQty_Complete, ChildWO, IssueQty, Production_Qty, Qty_Complete FROM mlBOM AS mlBOM1 ORDER BY FinishedWO, ParentWO, ChildWO

The query executes in 20-30 seconds when I restrict the dataset to circa 500 rows but with the full dataset of circa 2000 rows it times out.

What I am trying to do is similar to a recursive Bill of materials.
1. The Parent Work Order (ParentWO) has Manufactured Parts issued to it (ChildWO).
2. These Child Work Orders can themselves be Parent Work Orders if they have Manufactured Parts issued to them.

I am trying to write a recursive statement that will show:
FinishedWO, ParentWO, PProduction_Qty, PQty_Complete, ChildWO, IssueQty, ProductionQty, Qty_Complete

Where PProduction_Qty is the Planned Production Qty of the FinishedWO
Where PQty_Complete is the Qty Complete of the FinishedWO
Where IssueQty is the quantity of the Child Work Order issue to the Parent Work Order
Where Production_Qty is the Planned Production Qty of the ChildWO
Where Qty_Complete is the Qty Complete of the ChildWO

This will allow me to drill down into these deep Bill Of Materials when in production to determine real cost of sale based on Labour, Material and Subcontract costs that are posted to the Child Work Orders.
Thank you in advance.
Roberto.

===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable
(
ParentWO varchar(50),
PProduction_Qty float,
PQty_Complete float,
ChildWO varchar(50),
IssueQty int,
Production_Qty float,
Qty_Complete float
)

--===== Setup any special required conditions especially where dates are concerned--===== All Inserts into the IDENTITY column

--===== Insert the test data into the test table
INSERT INTO #mytable
(ParentWO, PProduction_Qty, PQty_Complete, ChildWO, IssueQty, ProductionQty, Qty_Complete)
SELECT 2088/1.1,40,40,2088/1.1.1,25,40,40 UNION ALL
SELECT 2088/1.1,40,40,2088/1.1.1,14,40,40 UNION ALL
SELECT 2088/1.1,40,40,2088/1.1.2,14,40,40 UNION ALL
SELECT 2088/1.1,40,40,2088/1.1.2,1,40,40 UNION ALL
SELECT 2088/1.1.1,40,40,2088/1.1.1.1,40,40,40 UNION ALL
SELECT 2088/1.1.2,40,40,2088/1.1.2.1,40,40,40 UNION ALL
SELECT 1990/2.1,25,25,1617/1.1.4,1,50,50 UNION ALL
SELECT 1990/2.1,25,25,1617/1.1.4,3,50,50 UNION ALL
SELECT 1990/2.1,25,25,1617/1.1.4,4,50,50 UNION ALL
SELECT 1990/2.1,25,25,1617/1.1.8,3,50,50 UNION ALL
SELECT 1990/2.1,25,25,1617/1.1.8,2,50,50 UNION ALL
SELECT 1990/2.1,25,25,1617/1.1.8,11,50,50 UNION ALL
SELECT 1990/2.1,25,25,1617/1.1.8,8,50,50 UNION ALL
SELECT 1990/2.1,25,25,1990/2.1.1,17,50,50 UNION ALL
SELECT 1990/2.1,25,25,1990/3.1.2,1,50,50 UNION ALL
SELECT 1990/2.1.1,50,50,1990/2.1.1.1,50,120,120 UNION ALL
SELECT 1990/2.1.2,50,50,1990/2.1.2.1,50,120,120 UNION ALL
SELECT 1990/3.1,25,25,1617/1.1.4,2,50,50 UNION ALL
SELECT 1990/3.1,25,25,1990/2.1.1,8,50,50 UNION ALL
SELECT 1990/3.1,25,25,1990/2.1.1,15,50,50 UNION ALL
SELECT 1990/3.1,25,25,1990/3.1.2,24,50,50 UNION ALL
SELECT 1990/3.1,25,25,1990/3.1.2,1,50,50 UNION ALL
SELECT 1990/3.1.1,50,50,1990/2.1.1.1,43,120,120 UNION ALL
SELECT 1990/3.1.2,50,50,1990/2.1.2.1,50,120,120 UNION ALL
SELECT 1990/4.1,25,25,1990/2.1.2,25,50,50 UNION ALL
SELECT 1990/4.1,25,25,1990/3.1.1,25,50,50 UNION ALL
SELECT 1990/4.1.1,50,50,1990/3.1.1.1,30,120,120 UNION ALL
SELECT 1990/4.1.2,50,50,1990/3.1.2.1,30,120,120

--===== Set the identity insert back to normal

sgmunson SSC Guru Points: 110640 More actions · Answer 9

FYI, your code, as posted above, and then corrected by me only for the use of the temp table and the spelling of one of the field names (Production_Qty instead of ProductionQty), produced the following results:

FinishedWO ParentWO PProduction_Qty PQty_Complete ChildWO IssueQty Production_Qty Qty_Complete 1990/2.1 1990/2.1 25 25 1617/1.1.4 1 50 50 1990/2.1 1990/2.1 25 25 1617/1.1.4 3 50 50 1990/2.1 1990/2.1 25 25 1617/1.1.4 4 50 50 1990/2.1 1990/2.1 25 25 1617/1.1.8 3 50 50 1990/2.1 1990/2.1 25 25 1617/1.1.8 2 50 50 1990/2.1 1990/2.1 25 25 1617/1.1.8 11 50 50 1990/2.1 1990/2.1 25 25 1617/1.1.8 8 50 50 1990/2.1 1990/2.1 25 25 1990/2.1.1 17 50 50 1990/2.1 1990/2.1 25 25 1990/3.1.2 1 50 50 1990/2.1 1990/2.1.1 50 50 1990/2.1.1.1 50 120 120 1990/2.1 1990/3.1.2 50 50 1990/2.1.2.1 50 120 120 1990/2.1.1 1990/2.1.1 50 50 1990/2.1.1.1 50 120 120 1990/2.1.2 1990/2.1.2 50 50 1990/2.1.2.1 50 120 120 1990/3.1 1990/2.1.1 50 50 1990/2.1.1.1 50 120 120 1990/3.1 1990/2.1.1 50 50 1990/2.1.1.1 50 120 120 1990/3.1 1990/3.1 25 25 1617/1.1.4 2 50 50 1990/3.1 1990/3.1 25 25 1990/2.1.1 8 50 50 1990/3.1 1990/3.1 25 25 1990/2.1.1 15 50 50 1990/3.1 1990/3.1 25 25 1990/3.1.2 24 50 50 1990/3.1 1990/3.1 25 25 1990/3.1.2 1 50 50 1990/3.1 1990/3.1.2 50 50 1990/2.1.2.1 50 120 120 1990/3.1 1990/3.1.2 50 50 1990/2.1.2.1 50 120 120 1990/3.1.1 1990/3.1.1 50 50 1990/2.1.1.1 43 120 120 1990/3.1.2 1990/3.1.2 50 50 1990/2.1.2.1 50 120 120 1990/4.1 1990/2.1.2 50 50 1990/2.1.2.1 50 120 120 1990/4.1 1990/3.1.1 50 50 1990/2.1.1.1 43 120 120 1990/4.1 1990/4.1 25 25 1990/2.1.2 25 50 50 1990/4.1 1990/4.1 25 25 1990/3.1.1 25 50 50 1990/4.1.1 1990/4.1.1 50 50 1990/3.1.1.1 30 120 120 1990/4.1.2 1990/4.1.2 50 50 1990/3.1.2.1 30 120 120 2088/1.1 2088/1.1 40 40 2088/1.1.1 25 40 40 2088/1.1 2088/1.1 40 40 2088/1.1.1 14 40 40 2088/1.1 2088/1.1 40 40 2088/1.1.2 14 40 40 2088/1.1 2088/1.1 40 40 2088/1.1.2 1 40 40 2088/1.1 2088/1.1.1 40 40 2088/1.1.1.1 40 40 40 2088/1.1 2088/1.1.1 40 40 2088/1.1.1.1 40 40 40 2088/1.1 2088/1.1.2 40 40 2088/1.1.2.1 40 40 40 2088/1.1 2088/1.1.2 40 40 2088/1.1.2.1 40 40 40 2088/1.1.1 2088/1.1.1 40 40 2088/1.1.1.1 40 40 40 2088/1.1.2 2088/1.1.2 40 40 2088/1.1.2.1 40 40 40

Is that what you wanted? Does it even properly reflect the parent/child relationship?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110640 More actions · Answer 10

FYI, do you realize that the same child WO appears as a child to more than one parent ??? This is not a good idea. Once a sizable number of work orders get multiple parents as well as multiple children, this could get ugly in one heck of a hurry. With your actual data, this may already be the case. With your sample data, your query ran on my 32GB Core i7 laptop in less than a second, so you may have other capacity constraints beyond the data issues I just mentioned. Also, there's another problem with your query. The "anchor" portion of your recursive CTE does not exclude child work orders. It simply grabs EVERY work order. If I limit that anchor portion of the query to add:

WHERE NOT EXISTS (SELECT 1 FROM #mytable AS MT2 WHERE MT2.ParentWO = MT.ChildWO)

Then I only get 16 rows back, but I still see the records showing the "multiple parent" child work order. I mention this so that you understand the concepts behind traversing a hierarchy. Typically, a recursive CTE will have a single highest level parent record, but having multiple highest level records is okay too. You don't want to start your anchor with every record in the table, however. You need to determine exactly which work orders constitute that "highest level" of your hierarchy. Then, the recursive portion should only be deriving the immediate children of the records gathered so far. If your table just doesn't meet that definition of a hierarchy, you may not be able to use that approach.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

robertopmorris SSC Eights! Points: 806 More actions · Answer 11

Steve

Unfortunately Child Work Orders will be produced and issued to more than one Parent Work Order - the thoughts behind this is that some Child Manufactured Parts are common to more than one Parent - therefore economies of scale deems it cost effective to batch produce. This does make the data very messy and could well be causing the slow down.
Is there a way I can restrict the data set to less Parent Work Orders eg - so that I only drill down Parent Work Orders that have been delivered to Customers within a given time scale. Other data exists which I could pull into to make this query possible?
EG - in the above example with Work Orders starting 2088/1.1 - only the 2088/1.1 is delivered to the Customer - all the other Work Orders starting with 2088/1.1 are Child Work Orders that will not be delivered to the end Customer.
I'm just not sure how to start the anchor with only certain records.
Thank you in advance.
Roberto.

sgmunson SSC Guru Points: 110640 More actions · Answer 12

robertopmorris - Wednesday, February 15, 2017 2:14 PM
Steve
Unfortunately Child Work Orders will be produced and issued to more than one Parent Work Order - the thoughts behind this is that some Child Manufactured Parts are common to more than one Parent - therefore economies of scale deems it cost effective to batch produce. This does make the data very messy and could well be causing the slow down.
Is there a way I can restrict the data set to less Parent Work Orders eg - so that I only drill down Parent Work Orders that have been delivered to Customers within a given time scale. Other data exists which I could pull into to make this query possible?
EG - in the above example with Work Orders starting 2088/1.1 - only the 2088/1.1 is delivered to the Customer - all the other Work Orders starting with 2088/1.1 are Child Work Orders that will not be delivered to the end Customer.
I'm just not sure how to start the anchor with only certain records.
Thank you in advance.
Roberto.

Add the WHERE clause I posted earlier as a starting point. That will at least limit you to work orders that don't have a parent. You could then add to that WHERE clause by adding some kind of date condition for those no parent work orders. With regard to the "messy" - to be honest, I think someone above you either has a screw loose or just has zero education about how data structures are supposed to work. You may very well already be in a position from which you will be UNABLE to make a query work that gives you the information you need because of that "messiness".

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Sue_H SSC Guru Points: 90860 More actions · Answer 13

robertopmorris - Wednesday, February 15, 2017 10:26 AM

I have tried to follow the guide as per the response from Sue and Jeff.

Can someone please advise I have have done this incorrectly so that I can revise.

Excellent job - thanks for putting those in there.
The new web software here is a bit picky so it's easiest to put all SQL statements in the SQL Code sections as then it doesn't loose any formatting, makes it easier to read.
Plenty of people follow along without posting so it's easier for them to work on the questions and learn things when those are posted. And other posters get an idea of how to post the questions as well.
Thanks again!

Sue

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 14

The posted data won't load. It's missing quotes.

Msg 102, Level 15, State 1, Line 23
Incorrect syntax near '.1'.
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near '.1'.
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near '.2'.
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near '.2'.
Msg 102, Level 15, State 1, Line 27
Incorrect syntax near '.1'.
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near '.2'.
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near '.4'.
Msg 102, Level 15, State 1, Line 30
Incorrect syntax near '.4'.
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near '.4'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near '.8'.
Msg 102, Level 15, State 1, Line 33
Incorrect syntax near '.8'.
Msg 102, Level 15, State 1, Line 34
Incorrect syntax near '.8'.
Msg 102, Level 15, State 1, Line 35
Incorrect syntax near '.8'.
Msg 102, Level 15, State 1, Line 36
Incorrect syntax near '.1'.
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near '.2'.
Msg 102, Level 15, State 1, Line 38
Incorrect syntax near '.1'.
Msg 102, Level 15, State 1, Line 39
Incorrect syntax near '.2'.
Msg 102, Level 15, State 1, Line 40
Incorrect syntax near '.4'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near '.1'.
Msg 102, Level 15, State 1, Line 42
Incorrect syntax near '.1'.
Msg 102, Level 15, State 1, Line 43
Incorrect syntax near '.2'.
Msg 102, Level 15, State 1, Line 44
Incorrect syntax near '.2'.
Msg 102, Level 15, State 1, Line 45
Incorrect syntax near '.1'.
Msg 102, Level 15, State 1, Line 46
Incorrect syntax near '.2'.
Msg 102, Level 15, State 1, Line 47
Incorrect syntax near '.2'.
Msg 102, Level 15, State 1, Line 48
Incorrect syntax near '.1'.
Msg 102, Level 15, State 1, Line 49
Incorrect syntax near '.1'.
Msg 102, Level 15, State 1, Line 50
Incorrect syntax near '.2'.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)