Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 PreOrder Traversal Algorithm

Author  Topic 

udaaf
Starting Member

22 Posts

Posted - 2013-07-30 : 09:57:32
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

30421 Posts

Posted - 2013-07-30 : 12:25:46
[code]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;[/code]


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

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-07-30 : 16:42:48
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-30 : 16:57:46
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. ;)
Go to Top of Page

udaaf
Starting Member

22 Posts

Posted - 2013-07-30 : 21:07:20
@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

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-31 : 03:25:16
[code]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;[/code]


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

udaaf
Starting Member

22 Posts

Posted - 2013-07-31 : 04:03:35
@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

30421 Posts

Posted - 2013-07-31 : 09:34:34
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

22 Posts

Posted - 2013-07-31 : 20:52:26
@SwePeso,

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

Go to Top of Page

udaaf
Starting Member

22 Posts

Posted - 2013-08-01 : 00:56:49
@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

30421 Posts

Posted - 2013-08-01 : 02:28:55
Use the query in cteParents.



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

udaaf
Starting Member

22 Posts

Posted - 2013-08-01 : 02:33:09
@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

30421 Posts

Posted - 2013-08-01 : 03:06:53
[code]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;[/code]


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

udaaf
Starting Member

22 Posts

Posted - 2013-08-01 : 03:29:52
@SwePeso,

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

udaaf
Starting Member

22 Posts

Posted - 2013-08-01 : 21:13:31
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

30421 Posts

Posted - 2013-08-02 : 02:12:22
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

22 Posts

Posted - 2013-08-02 : 03:08:30
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

30421 Posts

Posted - 2013-08-02 : 03:32:24
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

22 Posts

Posted - 2013-08-02 : 04:17:02
@SwePeso,

Thank you so much for your time and code .
Solved.
And I'll for another schema.
Go to Top of Page
   

- Advertisement -