CTE with multiple tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » CTE with multiple tables

CTE with multiple tables

Rate Topic

Display Mode

Topic Options

Author

Message

Bron Tamulis

Posted Sunday, December 30, 2012 2:44 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 31, 2012 4:48 PM
Points: 117, Visits: 78

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.

Post #1401271

CELKO

Posted Sunday, December 30, 2012 6:02 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

How can a quantity be NUMERIC(19, 5) and not an INTEGER? Likewise, why are machine times expressed to less than a nano-second? 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.

A table should have all of the attributes of the entity in models. That would seem to mean that you need the assemblies in one table, thus:

CREATE TABLE Assemblies
(assembly_name CHAR (31) NOT NULL PRIMARY KEY
bom_type INTEGER NOT NULL,
bom_qty INTEGER NOT NULL
CHECK (bom_qty > 0),
labor_time DECIMAL (5, 2) NOT NULL,
machine_time DECIMAL (5, 2) NOT NULL,
machine_id CHAR(31) NOT NULL);

The parts explosion is a relationship among assemblies; it gets its own table, of course. You can add more constraints, but here is the basic nested sets table:

CREATE TABLE Parts_Explosion
(assembly_name CHAR (31) NOT NULL,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt));

To get you started, a tree for FG-A would be:

('FG-A' 1, 10 ),
('SUBASSY-A', 1, 4),
('RAW-A', 2, 3),
('SUBASSY-B', 5, 8),
('RAW-B', 6, 7);

See how subordination is shown by nesting within the (lft, rgt) range? Much faster and easier than a cursor and loop! Now add columns for the labor and machine time, and quantity.

I have a BOM example in TREES & HIERARCHIES which I can not going to post here. The idea is to start at the leaf nodes and move up to the root, computing (quantity * weight) at each level.

SELECT BOM_2.assembly_name,
SUM(labor_time * bom_qty) AS labor_time_tot,
etc.
FROM BOM AS BOM_1, BOM AS BOM_2,
Salaries AS S1
WHERE BOM_1.lft BETWEEN BOM_2.lft AND BOM_2.rgt
AND S1.assembly_name = BOM_2.assembly_name
GROUP BY BOM_2.assembly_name;

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1401277

Bron Tamulis

Posted Monday, December 31, 2012 1:03 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 31, 2012 4:48 PM
Points: 117, Visits: 78

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........

Post #1401489

Evil Kraig F

Posted Monday, December 31, 2012 1:55 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 5,678, Visits: 6,130

@CELKO:
*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
	ON	r.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
	ON	r.Inclusion = t2.Parent
GROUP BY
	r.Construct,
	t2.MachineID
ORDER BY
	r.construct,
	t2.MachineID

- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1401498

Bron Tamulis

Posted Monday, December 31, 2012 2:44 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 31, 2012 4:48 PM
Points: 117, Visits: 78

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!

Post #1401511

Bron Tamulis

Posted Monday, December 31, 2012 4:50 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 31, 2012 4:48 PM
Points: 117, Visits: 78

Craig - I tested your solution for a multi-level BOM and it worked great.

Thanks for your assistance..........

Post #1401525

Evil Kraig F

Posted Wednesday, January 02, 2013 12:27 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 5,678, Visits: 6,130

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.

Post #1402037

« Prev Topic | Next Topic »

Permissions