February 16, 2017 at 2:10 am
Jeff Moden - Wednesday, February 15, 2017 10:37 PM
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'.
.
.
.
-- Fix all the errors in Roberto's sample data script
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable
CREATE TABLE #mytable (
ParentWO varchar(50),
PProduction_Qty float,
PQty_Complete float,
ChildWO varchar(50),
IssueQty int,
Production_Qty float,
Qty_Complete float)
INSERT INTO #mytable
(ParentWO, PProduction_Qty, PQty_Complete, ChildWO, IssueQty, Production_Qty, 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;
-- Dedupe the data (haven't we been here before?) into a new #temp table
IF OBJECT_ID('TempDB..#myDedupedTable','U') IS NOT NULL DROP TABLE #myDedupedTable
SELECT ParentWO, PProduction_Qty, PQty_Complete, ChildWO, Production_Qty, Qty_Complete, IssueQty = SUM(IssueQty)
INTO #myDedupedTable
FROM #mytable
GROUP BY ParentWO, PProduction_Qty, PQty_Complete, ChildWO, Production_Qty, Qty_Complete;
-- Examine the hierarchy using a series of left joins.
-- This is helpful for visualisation purposes
-- and may even be a practical proposition if the hierarchy
-- isn't too deep.
-- Begin with rows which have no parent (Haven't we been here before?),
-- push them to the left-hand side of the output with children to the right.
-- Don't be scared by '#', or credit it with magical attributes,
-- it's just a visual aid.
WITH TopLevelParents AS (
SELECT * FROM #myDedupedTable a
WHERE NOT EXISTS (SELECT 1 FROM #myDedupedTable b WHERE b.ChildWO = a.ParentWO)
)
SELECT l1.*, '#' '#', l2.*,'#' '#', l3.* --, '#' '#', l4.*
FROM TopLevelParents l1
LEFT JOIN #myDedupedTable l2 ON l2.ParentWO = l1.ChildWO
LEFT JOIN #myDedupedTable l3 ON l3.ParentWO = l2.ChildWO
--LEFT JOIN #myDedupedTable l4 ON l4.ParentWO = l3.ChildWO NOT NECESSARY
ORDER BY l1.ParentWO, l1.ChildWO;
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
February 17, 2017 at 11:32 am
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable
CREATE TABLE #mytable (
ParentWO varchar(50),
PProduction_Qty float,
PQty_Complete float,
ChildWO varchar(50),
IssueQty int,
Production_Qty float,
Qty_Complete float)
INSERT INTO #mytable
(ParentWO, PProduction_Qty, PQty_Complete, ChildWO, IssueQty, Production_Qty, Qty_Complete)
SELECT '1990/6.1',25,25,'1990/2.1.2',7,50,50 UNION ALL
SELECT '1990/6.1',25,25,'1990/3.1.1',7,50,50 UNION ALL
SELECT '1990/6.1',25,25,'1990/4.1.1',15,50,50 UNION ALL
SELECT '1990/6.1',25,25,'1990/4.1.2',1,50,50 UNION ALL
SELECT '1990/2.1.2',50,50,'1990/2.1.2.1',50,120,120 UNION ALL
SELECT '1990/3.1.1',50,50,'1990/2.1.1.1',43,120,120 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 UNION ALL;
My SQL Code as follows:WITH mlBOM AS (SELECT CAST(0 AS int) AS [Level], ParentWO AS FinishedWO, ParentWO, PProduction_Qty, PQty_Complete, ChildWO, IssueQty, Production_Qty,
Qty_Complete
FROM dbo.RM_WorkOrdersSLGroup
UNION ALL
SELECT d.[Level] + 1 AS [Level], 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 [Level], FinishedWO, ParentWO, PProduction_Qty, PQty_Complete, ChildWO, IssueQty, Production_Qty, Qty_Complete
FROM mlBOM AS mlBOM1
WHERE (FinishedWO = '1990/6.1')
ORDER BY [Level], FinishedWO, ParentWO, ChildWO
The above data set is a real example of a Job that I need to drill down into to derive total cost of sale.
This code returns the following:
| [Level] 0 | FinishedWO 1990/6.1 | ParentWO 1990/6.1 | PProduction_Qty 25 | PQty_Complete 25 | ChildWO 1990/2.1.2 | IssueQty 7 | Production_Qty 50 | Qty_Complete 50 |
| 0 | 1990/6.1 | 1990/6.1 | 25 | 25 | 1990/3.1.1 | 7 | 50 | 50 |
| 0 | 1990/6.1 | 1990/6.1 | 25 | 25 | 1990/4.1.1 | 15 | 50 | 50 |
| 0 | 1990/6.1 | 1990/6.1 | 25 | 25 | 1990/4.1.2 | 1 | 50 | 50 |
| 1 | 1990/6.1 | 1990/2.1.2 | 50 | 50 | 1990/2.1.2.1 | 50 | 120 | 120 |
| 1 | 1990/6.1 | 1990/3.1.1 | 50 | 50 | 1990/2.1.1.1 | 43 | 120 | 120 |
| 1 | 1990/6.1 | 1990/4.1.1 | 50 | 50 | 1990/3.1.1.1 | 30 | 120 | 120 |
| 1 | 1990/6.1 | 1990/4.1.2 | 50 | 50 | 1990/3.1.2.1 | 30 | 120 | 120 |
At level 0 I can correctly calculate the cost of sale by dividing the total cost of ChildWO by Qty_Complete, then multiply by IssueQty, then divide by PQty_Complete.
However at level 1 the same logic will yield the wrong results;
In the first level 1 line that I have highlighted I need to calculate the total cost of the ChildWO then divide by Qty_Complete - this is okay - but the next calculation should be to multiply by the IssueQty of the ParentWO, then divide by the PQtyComplete of the FinishedWO.
Is there a way I can code this in SQL to output the correct dataset.
The starting dataset is a Sql View I have created from numerous other tables within the Db.
I really hope this makes sense - and that somebody can offer some assistance.
Thank you in advance.
Roberto
February 17, 2017 at 12:11 pm
robertopmorris - Friday, February 17, 2017 11:32 AM
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable
CREATE TABLE #mytable (
ParentWO varchar(50),
PProduction_Qty float,
PQty_Complete float,
ChildWO varchar(50),
IssueQty int,
Production_Qty float,
Qty_Complete float)
INSERT INTO #mytable
(ParentWO, PProduction_Qty, PQty_Complete, ChildWO, IssueQty, Production_Qty, Qty_Complete)
SELECT '1990/6.1',25,25,'1990/2.1.2',7,50,50 UNION ALL
SELECT '1990/6.1',25,25,'1990/3.1.1',7,50,50 UNION ALL
SELECT '1990/6.1',25,25,'1990/4.1.1',15,50,50 UNION ALL
SELECT '1990/6.1',25,25,'1990/4.1.2',1,50,50 UNION ALL
SELECT '1990/2.1.2',50,50,'1990/2.1.2.1',50,120,120 UNION ALL
SELECT '1990/3.1.1',50,50,'1990/2.1.1.1',43,120,120 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 UNION ALL;My SQL Code as follows:
WITH mlBOM AS (SELECT CAST(0 AS int) AS [Level], ParentWO AS FinishedWO, ParentWO, PProduction_Qty, PQty_Complete, ChildWO, IssueQty, Production_Qty,
Qty_Complete
FROM dbo.RM_WorkOrdersSLGroup
UNION ALL
SELECT d.[Level] + 1 AS [Level], 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 [Level], FinishedWO, ParentWO, PProduction_Qty, PQty_Complete, ChildWO, IssueQty, Production_Qty, Qty_Complete
FROM mlBOM AS mlBOM1
WHERE (FinishedWO = '1990/6.1')
ORDER BY [Level], FinishedWO, ParentWO, ChildWOThe above data set is a real example of a Job that I need to drill down into to derive total cost of sale.
This code returns the following:
[Level]
0FinishedWO
1990/6.1ParentWO
1990/6.1PProduction_Qty
25PQty_Complete
25ChildWO
1990/2.1.2IssueQty
7Production_Qty
50Qty_Complete
500 1990/6.1 1990/6.1 25 25 1990/3.1.1 7 50 50 0 1990/6.1 1990/6.1 25 25 1990/4.1.1 15 50 50 0 1990/6.1 1990/6.1 25 25 1990/4.1.2 1 50 50 1 1990/6.1 1990/2.1.2 50 50 1990/2.1.2.1 50 120 120 1 1990/6.1 1990/3.1.1 50 50 1990/2.1.1.1 43 120 120 1 1990/6.1 1990/4.1.1 50 50 1990/3.1.1.1 30 120 120 1 1990/6.1 1990/4.1.2 50 50 1990/3.1.2.1 30 120 120 At level 0 I can correctly calculate the cost of sale by dividing the total cost of ChildWO by Qty_Complete, then multiply by IssueQty, then divide by PQty_Complete.
However at level 1 the same logic will yield the wrong results;In the first level 1 line that I have highlighted I need to calculate the total cost of the ChildWO then divide by Qty_Complete - this is okay - but the next calculation should be to multiply by the IssueQty of the ParentWO, then divide by the PQtyComplete of the FinishedWO.
Is there a way I can code this in SQL to output the correct dataset.
The starting dataset is a Sql View I have created from numerous other tables within the Db.
I really hope this makes sense - and that somebody can offer some assistance.
Thank you in advance.
Roberto
I hate to keep beating a dead horse here, but your query is still doing recursion with the entire view being the anchor instead of just the parent work order. This makes no sense. I also still haven't seen a really good explanation of exactly how any one record in that view that you're querying is related to any other given record ... especially where the various quantity values come into play. Understanding the nature of your data is always the first priority, or all you end up doing is guessing on how things might be.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 17, 2017 at 12:25 pm
Steve:
Would you be available for a remote session in a couple of hours or some other time suitable for yourself - possibly a TeamViewer session.
I can show you the Db I'm working with and you could perhaps help me this way.
I have a meeting now but will be back banging my ahead against this problem in a couple of hours.
Thank you in advance.
Roberto.
February 17, 2017 at 12:56 pm
robertopmorris - Friday, February 17, 2017 12:25 PMSteve:Would you be available for a remote session in a couple of hours or some other time suitable for yourself - possibly a TeamViewer session.
I can show you the Db I'm working with and you could perhaps help me this way.
I have a meeting now but will be back banging my ahead against this problem in a couple of hours.Thank you in advance.
Roberto.
I have commitments through until rather late tonight, but maybe tomorrow... private message me and let's at least have a quick phone call to level set on expectations and determine a timeframe that can work.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply