December 30, 2012 at 2:44 pm
I've seen lots of examples of CTE's on line using a single table.......my situation I need to recurse thru 2 tables.
Table1 =
Create TABLE [dbo].[Table1](
[Parent] [char](31) NOT NULL,
[Child] [char](31) NOT NULL,
[BOMTYPE][int] NOT NULL,
[BOMQUANTITY] [numeric](19, 5) NOT NULL)
INSERT INTO Table1 ([Parent],[Child], [BOMTYPE], [BOMQUANTITY])
Values
('FG-A', 'RAW-A', '1', '2'),
('FG-A', 'RAW-B', '1', '1'),
('FG-A', 'SUBASSY-A', '1', '1'),
('FG-A', 'SUBASSY-B', '1', '1'),
('FG-B', 'SUBASSY-A', '1', '1'),
('FG-B', 'RAW-A', '2', '1'),
('FG-C', 'RAW-A', '1', '5'),
('FG-D', 'SUBASSY-A', '1', '1'),
('SUBASSY-A', 'ITEM-B', '1', '1')
('SUBASSY-B', 'ITEM-A', '1', '1')
Table2 =
Create Table [Table2] ([Parent] [char] (31) NOT NULL,
[SEQ] [int] NOT NULL,
[LABRTIME] [numeric] (19,5) NOT NULL,
[MACHINEID] [char] (31) NOT NULL,
[MACHTIME] [numeric] (19,5))
INSERT INTO [Table2]
([Parent]
,[SEQ]
,[LABRTIME]
,[MACHINEID]
,[MACHTIME])
VALUES
('FG-A','10','1.25','ASSEM','4.5'),
('FG-A','20','.50','CLEAN','3'),
('SUBASSY-A','10','1.25','MACHPKG','.75'),
('SUBASSY-A','20','1.00','ASSEM','4.5'),
('FG-B','10','.25','CLEAN','4.0'),
('FG-B','20','1.25','ASSEM','2.0'),
('FG-C','10','.75','ASSEM','4.5'),
('FG-C','20','.25','OUTSOURCED','.5'),
('FG-D','10','.85','MACHPKG','1.0'),
('SUBASSY-B','10','1.25','CLEAN','2.0'),
('SUBASSY-B','20','1.00','ASSEM','2.0')
My desired outcome is:
Parent, MachineID, MachineTime (sum)
A single record for every Parent/MACHINEID combination from Table1 and Table2 - then a single record for each Machine ID from Table 2, with a summary of MachineTime that includes child (of Parent) where BOMTYPE = '1'
Example: FG-A is hardest to calculate -
FG-A has 4 child (RAW-A, RAW-B, SUBASSY-A, and SUBASSY-B)
RAW-A and RAW-B do not have any records in Table2 so we do nothing with those records...however, SUBASSY-A and SUBASSY-B do have records in Table2 which need to be combined with FG-A
Desired Output for FG-A:
Parent MACHINEID MACHINETIME
FG-A ASSEM 9.0 (4.5 + 4.5 (from SUBASSY-A))
FG-A CLEAN 5.0 (3.0 + 2.0 (from SUBASSY-B))
FG-A MACHPKG .75 (this comes from SUBASSY-A)
When we loop through Table1 we need to loop multiple times - in other words there could be additional 'subassys' that are part of subassys that are part of other parent...........
the entire output would be:
Parent MACHINEID MACHINETIME
FG-A ASSEM 9.0
FG-A CLEAN 5.0
FG-A MACHPKG .75
FG-B ASSEM 6.5
FG-B CLEAN 4.0
FG-B MACHPKG .75
FG-D ASSEM 4.5
FG-D MACHPKG 1.75
SUBASSY-A ASSEM 4.5
SUBASSY-A MACHPKG .75
SUBASSY-B ASSEM 2.0
SUBASSY-B CLEAN 2.0
Can this be done with a CTE? Any assistance is appreciated in advance.
December 31, 2012 at 1:03 pm
Thanks for the reply.
My two tables are the readers digest version of actual ERP software tables. I didn't bother stating primary keys or NOT Nulls.....(sorry)
Both machinetime and labortime (you thought should not be numeric) are in fact numeric. They simply store the total time (in hours) it takes to to something.
I was hoping in my posting to learn something about CTE - I researched online found some samples - but everything was a single table.
I'm pretty confident I could write a CTE to deal with table1 (Bills of Materials) - i just can't visualize how I could use the results to make a calculation (summary) of the records in Table2 and return those records. Would a join work?
I don't have the luxury of creating my own tables.
I'll keep plugging away and maybe stumble upon a solution. I do appreciate your time - I am a SQL novice - I am totally self taught and thus, not well educated - I don't typically have to perform challenging tasks such as this.
I always try to respect the spirit of this forum by researching and attempting solutions before posting.
This forum has been great to and for me.
Typically I only have to create views, simple triggers and occasionally a simple stored procedure.
Most of your reply is way over my head, especially the utilization of .lft and .rgt.
Thanks again - I'll keep researching........
December 31, 2012 at 1:55 pm
*facepalm* Seriously?
CELKO (12/30/2012)
How can a quantity be NUMERIC(19, 5) and not an INTEGER?
Water: 1.5 gallons
Flour: .75 cups
It's a Bill of Materials table. Decimals are definately part of it.
Likewise, why are machine times expressed to less than a nano-second?
It does no harm and the fifth decimal allows for avoiding rounding issues if important, which is something that particular systems report using so they are accurate to the nanosecond, and there's no reason to force a mid-point data type conversion.
None of these things are tables at all! No keys! Rows are not records [sic], totally different concept. We also hate looping; it is procedural and SQL is declarative.
Oh here we go again. Also, lose the "WE". Not all of us like you. Be more explicit.
They are tables. Lacking keys isn't helping but these are also example entries, not full declarations, as you well know and are justing ranting about to be a pain. Rows, records, whatever. So, without looping, tell me, how do you traverse an Org table? Either you loop during the creation and update, or you loop during the read. SOMEWHERE in an org table, you loop. Nested Sets or recursive heirarchy, you will loop SOMEWHERE.
Please, take 2013 off or something? Perhaps a new resolution: I will not berate newbies for stuff that won't blow up their production systems and for not being completely anal about building sample data sets?
Bron, the rest of Celko's post is actually useful as an introduction to Nested Sets theory, but you'll need to seriously go for a long wiki-walk and a bunch of blogs to get that under your belt and understood. It's definately good to know, however. It's particularly much faster for lookups than recusion org-trees when the tree it self is fairly non-changing. All the loop/recusion/setup work is done up front, and then it's a simple quick-read to get anything else. If you add/alter things, that's where the code goes deep to keep it all straight and to 'move' things around. It is much faster for the 99.9% read type of hierarchies though.
Your sample data was untested, as you missed a comma, so your value for FG-A ASSEM is a bit off, it's 11 and not 9, due to Subassy inclusion. This is the bare-bones for what you need, and I don't believe it'll handle multiple layers of the heirarchy well, partially because there's no parent record recording the parent as a subcomponent of itself. It will, however, get you started.
If you have multiple heirarchy examples and would like more assistance, please set up that sample data and I'll see if I (or others, if they have time) can expand this example. Running a bit low on time today.
;with Construct AS
(SELECT DISTINCT
Parent AS Construct
FROM
Table1
),
rCTE AS
(SELECT
c.Construct,
t1.Child AS Inclusion
FROM
Construct AS c
JOIN
Table1 AS t1
ON c.Construct = t1.Parent
UNION ALL
SELECT
r.Construct,
t1.Child AS Inclusion
FROM
rCTE as r
JOIN
Table1 AS t1
ONr.Inclusion = t1.Parent
)
/*
SELECT * FROM rCTE
ORDER BY Construct, Inclusion
*/
SELECT
r.Construct,
t2.MachineID,
SUM( t2.MachTime) AS SumTime
FROM
( SELECT Construct, Construct AS Inclusion FROM Construct
UNION ALL
SELECT Construct, Inclusion FROM rCTE
) AS r
JOIN
Table2 AS t2
ONr.Inclusion = t2.Parent
GROUP BY
r.Construct,
t2.MachineID
ORDER BY
r.construct,
t2.MachineID
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 31, 2012 at 2:44 pm
Okay - as Craig was posting his reply (thank you very much) I wrote my first CTE!!! - I created a string (thinking I might be able to use in multi-level BOMs?) that adds the parent to the child.
With IndentedBOM(ItemNumber, Component, Sorting, Level1, Quantity)
As
(
Select Parent, Child, Cast(RTrim(Parent) + '--'+ Child As Varchar(Max)) Sorting, 1 Level1, Cast(BOMQUANTITY As INT) Quantity
From
(
Select Parent, Child, BOMQUANTITY
From Table1
Where BOMTYPE = 1
) A
Union All
Select Parent, Component, Cast(Rtrim(Parent)+'--'+Sorting As Varchar(Max)) Sorting, Level1+1, Cast(B.BOMQUANTITY As INT) * Cast(C.Quantity As INT) Quantity
From
(
Select Parent, Child, BOMQUANTITY
From Table1
Where BOMTYPE = 1
) B
Join IndentedBOM C on B.Child = C.ItemNumber
)
SELECT * FROM IndentedBom
ORDER BY LEVEL1
This returns a Parent, Child, Sorting (string combining) Level1 and QUANTITY.
I will test Craigs solution for a multi-level bom.............
Thanks Craig and Lynn for your input...........hopefully I can learn something today.....last knowledge of 2012!
December 31, 2012 at 4:50 pm
Craig - I tested your solution for a multi-level BOM and it worked great.
Thanks for your assistance..........
January 2, 2013 at 12:27 pm
Bron Tamulis (12/31/2012)
Craig - I tested your solution for a multi-level BOM and it worked great.Thanks for your assistance..........
Glad to hear and my pleasure. I just don't always trust my code until I've tested it, particularly with recursion. It can do some funny things unless you really sit down and work every inch of the logic out in your head.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply