Bill of material (SQL2000)

Hi,
Does anyone got a really good/fast example on how to do a BOM with unlimited
levels in SQL2000
- without using cursors :)
2 tables (one for items, one for parent/child relations)
In SQL2005 we of course are blessed with the new CTE, not in SQL2000 :-/
In advance, thanks...
Kr. Sorenhow do you want your data output? just a SP that returns a list of all
materials a particular item requires, right down the tree?
if so then the only way I can think of without cursors is using global
temporary tables and recursively called stored procedures to populate
it (but then you might as well use a cursor for that).
if you're wanting columns of data to represent the tree structure then
it's Dynamic SQL you'll need|||Soren, here goes:
-- DDL & Sample Data for Parts, BOM
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.BOM') IS NOT NULL
DROP TABLE dbo.BOM;
GO
IF OBJECT_ID('dbo.Parts') IS NOT NULL
DROP TABLE dbo.Parts;
GO
CREATE TABLE dbo.Parts
(
partid INT NOT NULL PRIMARY KEY,
partname VARCHAR(25) NOT NULL
);
INSERT INTO dbo.Parts(partid, partname) VALUES( 1, 'Black Tea');
INSERT INTO dbo.Parts(partid, partname) VALUES( 2, 'White Tea');
INSERT INTO dbo.Parts(partid, partname) VALUES( 3, 'Latte');
INSERT INTO dbo.Parts(partid, partname) VALUES( 4, 'Espresso');
INSERT INTO dbo.Parts(partid, partname) VALUES( 5, 'Double Espresso');
INSERT INTO dbo.Parts(partid, partname) VALUES( 6, 'Cup Cover');
INSERT INTO dbo.Parts(partid, partname) VALUES( 7, 'Regular Cup');
INSERT INTO dbo.Parts(partid, partname) VALUES( 8, 'Stirrer');
INSERT INTO dbo.Parts(partid, partname) VALUES( 9, 'Espresso Cup');
INSERT INTO dbo.Parts(partid, partname) VALUES(10, 'Tea Shot');
INSERT INTO dbo.Parts(partid, partname) VALUES(11, 'Milk');
INSERT INTO dbo.Parts(partid, partname) VALUES(12, 'Coffee Shot');
INSERT INTO dbo.Parts(partid, partname) VALUES(13, 'Tea Leaves');
INSERT INTO dbo.Parts(partid, partname) VALUES(14, 'Water');
INSERT INTO dbo.Parts(partid, partname) VALUES(15, 'Sugar Bag');
INSERT INTO dbo.Parts(partid, partname) VALUES(16, 'Ground Coffee');
INSERT INTO dbo.Parts(partid, partname) VALUES(17, 'Coffee Beans');
CREATE TABLE dbo.BOM
(
partid INT NOT NULL REFERENCES dbo.Parts,
assemblyid INT NULL REFERENCES dbo.Parts,
unit VARCHAR(3) NOT NULL,
qty DECIMAL(8, 2) NOT NULL,
UNIQUE(partid, assemblyid),
CHECK (partid <> assemblyid)
);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 1, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 2, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 3, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 4, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 5, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 6, 1, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 7, 1, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(10, 1, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 1, 'mL', 230.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 6, 2, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 7, 2, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(10, 2, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 2, 'mL', 205.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(11, 2, 'mL', 25.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 6, 3, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 7, 3, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(11, 3, 'mL', 225.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(12, 3, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 9, 4, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(12, 4, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 9, 5, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(12, 5, 'EA', 2.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(13, 10, 'g' , 5.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 10, 'mL', 20.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 12, 'mL', 20.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(16, 12, 'g' , 15.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(17, 16, 'g' , 15.00);
GO
-- Creation Script for Function fn_partsexplosion
---
-- Function: fn_partsexplosion, Parts Explosion
--
-- Input : @.root INT: Root part id
--
-- Output : @.PartsExplosion Table:
-- id and level of contained parts of input part
-- in all levels
--
-- Process : * Insert into @.PartsExplosion row of input root part
-- * In a loop, while previous insert loaded more than 0 rows
-- insert into @.PartsExplosion next level of parts
---
USE tempdb;
GO
IF OBJECT_ID('dbo.fn_partsexplosion') IS NOT NULL
DROP FUNCTION dbo.fn_partsexplosion;
GO
CREATE FUNCTION dbo.fn_partsexplosion(@.root AS INT)
RETURNS @.PartsExplosion Table
(
partid INT NOT NULL,
qty DECIMAL(8, 2) NOT NULL,
unit VARCHAR(3) NOT NULL,
lvl INT NOT NULL,
n INT NOT NULL IDENTITY, -- surrogate key
UNIQUE CLUSTERED(lvl, n) -- Index will be used to filter lvl
)
AS
BEGIN
DECLARE @.lvl AS INT;
SET @.lvl = 0; -- Initialize level counter with 0
-- Insert root node to @.PartsExplosion
INSERT INTO @.PartsExplosion(partid, qty, unit, lvl)
SELECT partid, qty, unit, @.lvl
FROM dbo.BOM
WHERE partid = @.root;
WHILE @.@.rowcount > 0 -- while previous level had rows
BEGIN
SET @.lvl = @.lvl + 1; -- Increment level counter
-- Insert next level of subordinates to @.PartsExplosion
INSERT INTO @.PartsExplosion(partid, qty, unit, lvl)
SELECT C.partid, P.qty * C.qty, C.unit, @.lvl
FROM @.PartsExplosion AS P -- P = Parent
JOIN dbo.BOM AS C -- C = Child
ON P.lvl = @.lvl - 1 -- Filter parents from previous level
AND C.assemblyid = P.partid;
END
RETURN;
END
GO
-- Parts Explosion
SELECT P.partid, P.partname, PE.qty, PE.unit, PE.lvl
FROM dbo.fn_partsexplosion(2) AS PE
JOIN dbo.Parts AS P
ON P.partid = PE.partid;
Output:
partid partname qtyn unit lvl
-- -- -- -- --
2 White Tea 1.00n EA 0
6 Cup Cover 1.00n EA 1
7 Regular Cup 1.00n EA 1
10 Tea Shot 1.00n EA 1
14 Water 205.00n mL 1
11 Milk 25.00n mL 1
13 Tea Leaves 5.00n g 2
14 Water 20.00n mL 2
-- Parts Explosion, Aggregating Parts
SELECT P.partid, P.partname, PES.qty, PES.unit
FROM (SELECT partid, unit, SUM(qty) AS qty
FROM dbo.fn_partsexplosion(2) AS PE
GROUP BY partid, unit) AS PES
JOIN dbo.Parts AS P
ON P.partid = PES.partid;
Output:
partid partname qty unit
-- -- -- --
2 White Tea 1.00 EA
6 Cup Cover 1.00 EA
7 Regular Cup 1.00 EA
10 Tea Shot 1.00 EA
13 Tea Leaves 5.00 g
11 Milk 25.00 mL
14 Water 225.00 mL
In SQL Server 2005 it would look like this:
-- CTE Solution for Parts Explosion
DECLARE @.root AS INT;
SET @.root = 2;
WITH PartsExplosionCTE
AS
(
-- Anchor member returns root part
SELECT partid, qty, unit, 0 AS lvl
FROM dbo.BOM
WHERE partid = @.root
UNION ALL
-- Recursive member returns next level of parts
SELECT C.partid, CAST(P.qty * C.qty AS DECIMAL(8, 2)),
C.unit, P.lvl + 1
FROM PartsExplosionCTE AS P
JOIN dbo.BOM AS C
ON C.assemblyid = P.partid
)
SELECT P.partid, P.partname, PE.qty, PE.unit, PE.lvl
FROM PartsExplosionCTE AS PE
JOIN dbo.Parts AS P
ON P.partid = PE.partid;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Soren S. Jorgensen" <nospam@.nodomain.com> wrote in message
news:ObORLLiXGHA.3936@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Does anyone got a really good/fast example on how to do a BOM with
> unlimited levels in SQL2000
> - without using cursors :)
> 2 tables (one for items, one for parent/child relations)
> In SQL2005 we of course are blessed with the new CTE, not in SQL2000 :-/
> In advance, thanks...
> Kr. Soren
>
>|||Get a copy of TREES & HIERARCHIES IN SQL for details then Google Nested
Sets model.

Bill of material

I have a table with data
create table a
(
a01 char(4),
a02 char(4),
a03 int
);
insert into a
values('a','b',1);
insert into a
values('a','c',1);
insert into a
values('x','c',1);
insert into a
values('c','g',1);
insert into a
values('b','h',1);
insert into a
values('h','k',1);
insert into a
values('b','k',1);
insert into a
values('g','k',1);
if my entry where value "k", i want show result
a01,a03
a 2
x 1
how to do ....
-
thanks!Get a copy of TREES & HIERARCHIES IN SQL for help.|||thanks a lot!
this my tree sql parent to find child ,but i want child to find parent,
do you have any idea?
ALTER PROCEDURE BOMIA_PPart
(
@.MODE NVARCHAR(12)
)
AS
SET NOCOUNT ON
CREATE TABLE #TEMP_A
(
LVL INT,
PARENT NVARCHAR(12) ,
PARTNO NVARCHAR(12) ,
M CHAR(2),
P CHAR(2),
PARTDESC NVARCHAR(40) ,
UNIT NVARCHAR(3) ,
USAGE REAL,
LOCATION NVARCHAR(18) ,
SXRQ NVARCHAR(10),
SSRQ NVARCHAR(10)
)
DECLARE @.ENDTREE INT
DECLARE @.NLVL INT
SELECT @.ENDTREE = 0
SELECT @.NLVL=1
INSERT INTO #TEMP_A(LVL,PARENT,PARTNO,P,USAGE,LOCATI
ON,SXRQ,SSRQ)
SELECT 1,IB001 ,IB003 ,IB031,IB004,IB011,IB008,IB009
FROM BOMIB
WHERE IB001=@.MODE
WHILE (@.ENDTREE =0 )
BEGIN
SELECT @.NLVL=@.NLVL+1
INSERT INTO #TEMP_A(LVL,PARENT,PARTNO,P,USAGE,LOCATI
ON,SXRQ,SSRQ)
SELECT @.NLVL,
A.IB001 ,
A.IB003 ,
A.IB031,
A.IB004,
A.IB011,
A.IB008,
A.IB009
FROM BOMIB A, #TEMP_A B
WHERE A.IB001 =B.PARTNO
AND B.LVL=@.NLVL -1
AND B.P!='1'
IF NOT EXISTS (SELECT PARTNO COLLATE database_default FROM #TEMP_A WHERE
LVL =@.NLVL)
SELECT @.ENDTREE=1
END
UPDATE #TEMP_A
SET M=AA070,
PARTDESC=AA020,
UNIT=AA050
FROM INVAA,#TEMP_A
WHERE AA010=PARTNO
SELECT
CAST(REPLICATE('.',LVL)+CAST(LVL AS NVARCHAR(2)) AS CHAR(12)) AS ',
PARENT AS ',
PARTNO AS ',
M,
CASE WHEN P='1' THEN '-'
WHEN P='2' THEN '+'
WHEN P='3' THEN '*'
ELSE '.'
END AS P,
PARTDESC AS ',
UNIT AS ',
ROUND(USAGE,2) AS ',
SXRQ AS ',
SSRQ AS ',
LOCATION AS '
FROM #TEMP_A
DROP TABLE #TEMP_A
SET NOCOUNT OFF
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1121909782.095848.154870@.g14g2000cwa.googlegroups.com...
> Get a copy of TREES & HIERARCHIES IN SQL for help.
>|||Did you bother to follow up my posting? TREES & HIERARCIES IN SQL
might be good read before you post again .sql

between dates and times

Hello,
I am new to SQL and am having a problem with dates and times.
I have a table with a 4 columns. Date, Time, Material, Qty
example;
01/12/05 04:00 steel 35
01/12/05 06:00 steel 60
01/12/05 23:40 steel 40
01/12/05 08:00 iron 25
02/12/05 05:00 steel 20
02/12/05 06:00 steel 50
I want to be able to look for matching rows that have a start & end date and
start & end time.
i.e start date 01/12/05 time >= 06:00
end date 02/12/05 time <= 05:59
The times are aways static. I ideally would like to see one row returned tha
t
summed the last column and combined the different dates like;
01/12/05 steel 120
01/12/05 iron 25
however if I searched for 01/12/05 and 03/12/05 I would see;
01/12/05 steel 120
01/12/05 iron 25
02/12/05 steel 50
Any help or guidance will be appreciated.What are the datatypes of the date and time columns. The ideal solution
would be to represent them as a single datetime value.
In any case, as a short term solution, check out the CONVERT function in SQL
Server Books Onlne. There are arguments that can help you convert a datetime
value to date-only string and a time-only string.
Anith