SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 PreOrder Traversal Algorithm
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

udaaf
Starting Member

Indonesia
20 Posts

Posted - 07/30/2013 :  09:57:32  Show Profile  Reply with Quote
Hi All,

I'm newbie here. I have some problem with query in SQL Server for counting total number of wgt per sub-assy and main assy.
Hope some here can help me to solve the problem.


/*Table Assembly*/
create table tblAssemblies
(
PartNumber varchar(20) not null ,
wgt real not null default 0,
ItemNumber varchar(20) not null primary key
)


insert into tblAssemblies (PartNumber,wgt ,ItemNumber ) 
values ('M-001',0, '0'),
       ('P-001',3,'1'),
       ('P-002',5,'2'),
       ('SU-001',0,'3'),
       ('P-001',3,'3.1'),
       ('P-002',10,'3.2'),
       ('P-003',10,'3.3')



SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 07/30/2013 :  12:25:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@Sample TABLE
	(
		PartNumber varchar(20) not null ,
		wgt real not null default 0,
		ItemNumber varchar(20) not null primary key
	);

INSERT	@Sample
	(
		PartNumber,
		wgt,
		ItemNumber
	)
VALUES	('M-001',  0, '0'  ),
	('P-001',  3, '1'  ),
	('P-002',  5, '2'  ),
	('SU-001', 0, '3'  ),
	('P-001',  3, '3.1'),
	('P-002', 10, '3.2'),
	('P-003', 12, '3.3');

-- Before
SELECT	*
FROM	@Sample;

-- SwePeso
WITH cteSource(ItemNumber, wgt)
AS (
	SELECT		s.ItemNumber,
			SUM(t.wgt) AS wgt
	FROM		@Sample AS s
	LEFT JOIN	@Sample AS t ON t.ItemNumber LIKE COALESCE(NULLIF(s.ItemNumber, '0'), '') + '%'
				AND t.wgt > 0
	WHERE		s.wgt = 0
	GROUP BY	s.ItemNumber
)
UPDATE		s
SET		s.wgt = q.wgt
FROM		@Sample AS s
INNER JOIN	cteSource AS q ON q.ItemNumber = s.ItemNumber;

-- After
SELECT	*
FROM	@Sample;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1673 Posts

Posted - 07/30/2013 :  16:42:48  Show Profile  Reply with Quote
How does your schema indicate that a part is related to an assembly or sub-assembly? How does it show that P-001 is a part of M-001? Your chart indicates that there might be a tree structure that you could traverse but I don't see the supporting structure in your schema.

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/30/2013 :  16:57:46  Show Profile  Reply with Quote
quote:
Originally posted by Bustaz Kool

How does your schema indicate that a part is related to an assembly or sub-assembly? How does it show that P-001 is a part of M-001? Your chart indicates that there might be a tree structure that you could traverse but I don't see the supporting structure in your schema.

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

Sadly, it is based on the ItemNumber. You will notice that 3.1, 3.2 and 3.3 are all under 3. 3 is really 0.3, but the 0 is silent. ;)

Edited by - Lamprey on 07/30/2013 16:59:04
Go to Top of Page

udaaf
Starting Member

Indonesia
20 Posts

Posted - 07/30/2013 :  21:07:20  Show Profile  Reply with Quote
@SwePeso

Thank for your code. It's work, but still have problem when the data is updated. For example I want to update weight for P-002 to 20. And re-execute your code. And the result is Weight number for SU-001 and M-001 isn't update.
Please see the attachment


Edited by - udaaf on 07/30/2013 21:08:54
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 07/31/2013 :  03:25:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@Sample TABLE
	(
		PartNumber varchar(20) not null ,
		wgt real not null default 0,
		ItemNumber varchar(20) not null primary key
	);

INSERT	@Sample
	(
		PartNumber,
		wgt,
		ItemNumber
	)
VALUES	('M-001',  0, '0'  ),
	('P-001',  3, '1'  ),
	('P-002',  5, '2'  ),
	('SU-001', 0, '3'  ),
	('P-001',  3, '3.1'),
	('P-002', 10, '3.2'),
	('P-003', 12, '3.3');

-- Before
SELECT	*
FROM	@Sample;

-- SwePeso
WITH cteParents(ItemNumber)
AS (
	SELECT		Item AS ItemNumber
	FROM		(
				SELECT	PARSENAME(CASE WHEN ItemNumber LIKE '%.%' THEN ItemNumber ELSE ItemNumber + '.x' END, 2) AS Item
				FROM	@Sample
			) AS d
	GROUP BY	Item
	HAVING		COUNT(*) >= 2
			OR Item = '0'
), cteSource(ItemNumber, wgt)
AS (
	SELECT		p.ItemNumber,
			SUM(t.wgt) AS wgt
	FROM		cteParents AS p
	LEFT JOIN	@Sample AS t ON t.ItemNumber LIKE COALESCE(NULLIF(p.ItemNumber, '0'), '') + '%'
				AND t.wgt > 0
	GROUP BY	p.ItemNumber
)
UPDATE		s
SET		s.wgt = q.wgt
FROM		@Sample AS s
INNER JOIN	cteSource AS q ON q.ItemNumber = s.ItemNumber;

-- After
SELECT	*
FROM	@Sample;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

udaaf
Starting Member

Indonesia
20 Posts

Posted - 07/31/2013 :  04:03:35  Show Profile  Reply with Quote
@SwePeso,

Thanks for your reply. But the result still incorrect.
SU-011 = 16
M-001 = 20

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 07/31/2013 :  09:34:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Works for me!
DECLARE	@Sample TABLE
	(
		PartNumber varchar(20) not null ,
		wgt real not null default 0,
		ItemNumber varchar(20) not null primary key
	);

INSERT	@Sample
	(
		PartNumber,
		wgt,
		ItemNumber
	)
VALUES	('M-001',  0, '0'  ),
	('P-001',  3, '1'  ),
	('P-002',  1, '2'  ),
	('SU-001', 0, '3'  ),
	('P-001',  3, '3.1'),
	('P-002',  1, '3.2'),
	('P-003', 12, '3.3');

-- Before
SELECT	*
FROM	@Sample;

-- SwePeso
WITH cteParents(ItemNumber)
AS (
	SELECT		Item AS ItemNumber
	FROM		(
				SELECT	PARSENAME(CASE WHEN ItemNumber LIKE '%.%' THEN ItemNumber ELSE ItemNumber + '.x' END, 2) AS Item
				FROM	@Sample
			) AS d
	GROUP BY	Item
	HAVING		COUNT(*) >= 2
			OR Item = '0'
), cteSource(ItemNumber, wgt)
AS (
	SELECT		p.ItemNumber,
			SUM(t.wgt) AS wgt
	FROM		cteParents AS p
	LEFT JOIN	@Sample AS t ON t.ItemNumber LIKE COALESCE(NULLIF(p.ItemNumber, '0'), '') + '%'
				AND t.wgt > 0
	GROUP BY	p.ItemNumber
)
UPDATE		s
SET		s.wgt = q.wgt
FROM		@Sample AS s
INNER JOIN	cteSource AS q ON q.ItemNumber = s.ItemNumber;

-- After
SELECT	*
FROM	@Sample;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

udaaf
Starting Member

Indonesia
20 Posts

Posted - 07/31/2013 :  20:52:26  Show Profile  Reply with Quote
@SwePeso,

Thanks for your reply. Here's the capture where describe the failure.

Go to Top of Page

udaaf
Starting Member

Indonesia
20 Posts

Posted - 08/01/2013 :  00:56:49  Show Profile  Reply with Quote
@SwePeso,

Finally I know which one make it fail.
Before execute

-- SwePeso
WITH cteParents(ItemNumber)
AS (
	SELECT		Item AS ItemNumber
	FROM		(
				SELECT	PARSENAME(CASE WHEN ItemNumber LIKE '%.%' THEN ItemNumber ELSE ItemNumber + '.x' END, 2) AS Item
				FROM	@Sample
			) AS d
	GROUP BY	Item
	HAVING		COUNT(*) >= 2
			OR Item = '0'
), cteSource(ItemNumber, wgt)
AS (
	SELECT		p.ItemNumber,
			SUM(t.wgt) AS wgt
	FROM		cteParents AS p
	LEFT JOIN	@Sample AS t ON t.ItemNumber LIKE COALESCE(NULLIF(p.ItemNumber, '0'), '') + '%'
				AND t.wgt > 0
	GROUP BY	p.ItemNumber
)
UPDATE		s
SET		s.wgt = q.wgt
FROM		@Sample AS s
INNER JOIN	cteSource AS q ON q.ItemNumber = s.ItemNumber;


Root and parent in Trees must be reset with value 0.
Could you explain me how to find root, parent and leaf with query.

Thanks before.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 08/01/2013 :  02:28:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Use the query in cteParents.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

udaaf
Starting Member

Indonesia
20 Posts

Posted - 08/01/2013 :  02:33:09  Show Profile  Reply with Quote
@SwePeso

I have execute this code and finally the table result is correct.


update tblAssy 
set wgt = 0
where ItemNumber in 
				(	SELECT item as ItemNumber
					FROM	(	select parsename(case when ItemNumber like '%.%' then ItemNumber else ItemNumber + '.x' end,2) as item
					from tblAssy 
				)	as d
					group by	item 
					having		COUNT (*) >=2
					or item = '0')

WITH cteParents(ItemNumber)
AS (
	SELECT		item as ItemNumber
	FROM		(
					select parsename(case when ItemNumber like '%.%' then ItemNumber else ItemNumber + '.x' end,2) as item
					from tblAssy 
				) as d
	group by item 
	having		COUNT (*) >=2
				or item = '0'
	), cteSource (ItemNumber, wgt)
AS (
	select		p.ItemNumber,
				sum(t.wgt) as wgt
	from		cteParents as p
	left join	tblAssy as t on t.ItemNumber LIKE COALESCE(NULLIF(p.ItemNumber, '0'), '') + '%'
				and t.wgt > 0
	group by	p.ItemNumber 
	)


And the another question is how to simplify the code above.

Many thanks,

Udaaf
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 08/01/2013 :  03:06:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@Sample TABLE
	(
		PartNumber varchar(20) not null ,
		wgt real not null default 0,
		ItemNumber varchar(20) not null primary key
	);

INSERT	@Sample
	(
		PartNumber,
		wgt,
		ItemNumber
	)
VALUES	('M-001',  99, '0'  ),
	('P-001',   3, '1'  ),
	('P-002',   1, '2'  ),
	('SU-001', 99, '3'  ),
	('P-001',   3, '3.1'),
	('P-002',   1, '3.2'),
	('P-003',  12, '3.3');

-- Before
SELECT	*
FROM	@Sample;

-- SwePeso
WITH cteParents(ItemNumber)
AS (
	SELECT		Item AS ItemNumber
	FROM		(
				SELECT	PARSENAME(CASE WHEN ItemNumber LIKE '%.%' THEN ItemNumber ELSE '0.' + ItemNumber END, 2) AS Item
				FROM	@Sample
			) AS d
	GROUP BY	Item
	HAVING		COUNT(*) >= 2
), cteSource(ItemNumber, wgt)
AS (
	SELECT		p.ItemNumber,
			SUM(t.wgt) AS wgt
	FROM		cteParents AS p
	LEFT JOIN	@Sample AS t ON t.ItemNumber LIKE COALESCE(NULLIF(p.ItemNumber, '0'), '') + '%'
				AND t.ItemNumber NOT IN (SELECT x.ItemNumber FROM cteParents AS x)
	GROUP BY	p.ItemNumber
)
UPDATE		s
SET		s.wgt = q.wgt
FROM		@Sample AS s
INNER JOIN	cteSource AS q ON q.ItemNumber = s.ItemNumber;

-- After
SELECT	*
FROM	@Sample;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

udaaf
Starting Member

Indonesia
20 Posts

Posted - 08/01/2013 :  03:29:52  Show Profile  Reply with Quote
@SwePeso,

Thanks a lot for your time .
Case Closed
Go to Top of Page

udaaf
Starting Member

Indonesia
20 Posts

Posted - 08/01/2013 :  21:13:31  Show Profile  Reply with Quote
Hi SwePeso,

I'm back again after try your code with another case/schema.
Here's new case :


DECLARE	@Sample TABLE
	(
		PartNumber varchar(20) not null ,
		wgt real not null default 0,
		ItemNumber varchar(20) not null primary key
	);

INSERT	@Sample
	(
		PartNumber,
		wgt,
		ItemNumber
	)
VALUES	('M-001',  0, '0'  ),
		('P-001',   2, '1'  ),
		('P-002',   2, '2'  ),
		('SU-001', 0, '3'  ),
		('SU-002',   0, '3.1'),
		('P-005',   1, '3.1.1'),
		('P-006',  3, '3.1.2'),
		('P-003',  4, '3.2'),
		('P-004',  5, '3.3');

-- Before
 --SELECT	*
 --FROM	@Sample;

-- SwePeso
WITH cteParents(ItemNumber)
AS (
	SELECT		Item AS ItemNumber
	FROM		(
				SELECT	PARSENAME(CASE WHEN ItemNumber LIKE '%.%' THEN ItemNumber ELSE '0.' + ItemNumber END, 2) AS Item
				FROM	@Sample
			) AS d
	GROUP BY	Item
	HAVING		COUNT(*) >= 2
), cteSource(ItemNumber, wgt)
AS (
	SELECT		p.ItemNumber,
			SUM(t.wgt) AS wgt
	FROM		cteParents AS p
	LEFT JOIN	@Sample AS t ON t.ItemNumber LIKE COALESCE(NULLIF(p.ItemNumber, '0'), '') + '%'
				AND t.ItemNumber NOT IN (SELECT x.ItemNumber FROM cteParents AS x)
	GROUP BY	p.ItemNumber
)
UPDATE		s
SET		s.wgt = q.wgt
FROM		@Sample AS s
INNER JOIN	cteSource AS q ON q.ItemNumber = s.ItemNumber;

-- After
SELECT	*
FROM	@Sample;


I have try query for find the parent. The result is incorrect.
Please see this one :

0
1
3

actually the result must ;
0
3
3.1

Could help me SwePeso ?
Thanks before
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 08/02/2013 :  02:12:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
So you have have as many dots as possible?
How about you tell us the business rules for parent-child relationship before we make more guesses?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

udaaf
Starting Member

Indonesia
20 Posts

Posted - 08/02/2013 :  03:08:30  Show Profile  Reply with Quote
Yes, I have so many dot as possible.
The business rules for parent-child relationship is depend with assembly model. Sometimes some part (P-xxx) can absorbed with sub assy (SU-xxx) direct to main assy (M-xxx).

I have try for change the structure data for item no. follow theory Materialized Path in SQL Pattern Vadim Tropashko. It's very easy for search parent, child. But I still can't understand the logic for counting weight amount per sub assy to main assy.
Hope you can help me for solve this problem.


insert into tblAssy 
(PartNumber ,wgt ,ItemNumber )
VALUES	        ('M-001',  0, '1'  ),
		('P-001',   2, '1.1'  ),
		('P-002',   2, '1.2'  ),
		('SU-001', 0, '1.3'  ),
		('SU-002',   0, '1.3.1'),
		('P-005',   1, '1.3.1.1'),
		('P-006',  3, '1.3.1.2'),
		('P-003',  4, '1.3.2'),
		('P-004',  5, '1.3.3');
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 08/02/2013 :  03:32:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
So, "M" and "SU" PartNumbers are "parents"?
DECLARE	@Sample TABLE
	(
		PartNumber VARCHAR(20) NOT NULL,
		wgt MONEY NOT NULL,
		ItemNumber VARCHAR(20) PRIMARY KEY CLUSTERED
	);

INSERT	@Sample
	(
		PartNumber,
		wgt,
		ItemNumber
	)
VALUES	('M-001',  0, '0'    ),
	('P-001',  2, '1'    ),
	('P-002',  2, '2'    ),
	('SU-001', 0, '3'    ),
	('SU-002', 0, '3.1'  ),
	('P-005',  1, '3.1.1'),
	('P-006',  3, '3.1.2'),
	('P-003',  4, '3.2'  ),
	('P-004',  5, '3.3'  );

-- Before
SELECT	*
FROM	@Sample;

-- SwePeso
WITH cteParents(ItemNumber)
AS (
	SELECT	ItemNumber
	FROM	@Sample
	WHERE	PartNumber LIKE 'M-%'
		OR PartNumber LIKE 'SU-%'
), cteSource(ItemNumber, wgt)
AS (
	SELECT		p.ItemNumber,
			SUM(COALESCE(s.wgt, 0)) AS wgt
	FROM		cteParents AS p
	LEFT JOIN	(
				SELECT	ItemNumber AS Original,
					CASE
						WHEN ItemNumber LIKE '%.%' THEN ItemNumber
						ELSE '0.' + ItemNumber
					END AS ItemNumber,
					wgt
				FROM	@Sample
			) AS s ON s.ItemNumber LIKE p.ItemNumber + '.%'
				OR p.ItemNumber = '0'
	WHERE		s.Original NOT IN (SELECT ItemNumber FROM cteParents)
	GROUP BY	p.ItemNumber
)
UPDATE		s
SET		s.wgt = q.wgt
FROM		@Sample AS s
INNER JOIN	cteSource AS q ON q.ItemNumber = s.ItemNumber;

-- After
SELECT	*
FROM	@Sample;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

udaaf
Starting Member

Indonesia
20 Posts

Posted - 08/02/2013 :  04:17:02  Show Profile  Reply with Quote
@SwePeso,

Thank you so much for your time and code .
Solved.
And I'll for another schema.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.15 seconds. Powered By: Snitz Forums 2000