MSSQL2000多版本BOM表计算产品成本(bom表重复使用到该半成品(同版本号))
该BOM表采用主、从表设计，当主、从表的BILLID相同时则表示同一阶BOM，
BOM表可能有多阶的，这里写出的数据比较简单，希望能循环计算出多阶BOM单价，
单价从最下阶开始往上累加计算得出。
可参考如下网址:http://bbs.csdn.net/topics/350119105
http://bbs.csdn.net/topics/350207921
主表: M_BOM
单据ID ， 母件 ， 数量 ， 版本号
BILLID , GOODSID , QTY , BOMVER
1 , A , 1 , A
2 , D , 1 , D
3 , D , 1 , D2
4 , A , 1 , A2
细表: M_BOMD
单据ID ，子件序号, 子件 , 数量 ，关联主表BILLID
BILLID , ITEMNO ,GOODSID , QTY , BOMID
1 , 1 , B , 1 , NULL
1 , 2 , C , 1 , NULL
1 , 3 , D , 1 , 2 ---这个关联主表M_BOM中BILLID=2
1 , 4 , D , 1 , 2 -----2014-10-07加入此部份,物料清单中重复使用半成品(同一版本号)
2 , 1 , E , 1 , NULL
2 , 2 , F , 1 , NULL
3 , 1 , E , 1 , NULL
3 , 2 , F , 2 , NULL
4 , 1 , B , 1 , NULL
4 , 2 , C , 1 , NULL
4 , 3 , D , 1 , 3 ---这个关联主表M_BOM中BILLID=3
货品单价表 GOODS
货品 , 单价
GOODSID , PRICE
A , 1
B , 1
C , 1
D , 1
E , 1
F , 1
求函数计算出如下结果：
母件 ,数量 , 版本号 , 序号 , 子件 , 子数 , 子版本名 , 单价 , 金额
MGOODIS ,MQTY , BOMVER , ITEMNO , GOODSID , QTY , BOMIDNAME , PRICE , AM
A , 1 , A , 1 , B , 1 , NULL , 1 , 1
A , 1 , A , 2 , C , 1 , NULL , 1 , 1
A , 1 , A , 3 , D , 1 , D , 2 , 2
A , 1 , A , 4 , D , 1 , D , 2 , 2
A , 1 , A2 , 1 , B , 1 , NULL , 1 , 1
A , 1 , A2 , 2 , C , 1 , NULL , 1 , 1
A , 1 , A2 , 3 , D , 1 , D2 , 3 , 3
---以下是coleling兄回复 ,但现在2014-10-07加入此部份,物料清单中重复使用半成品(同一版本号) 就有点小问题,计算该半成品(多版本号的)的单价不对.
if object_id('GetBom','TF') is not null
drop function GetBom
go
create function GetBom()
returns @tb table(MGoodsId Varchar(10), MQty int, BomVer Varchar(10), ItemNo int, GoodsId Varchar(10), Qty int, BomIdName Varchar(10), Price int, AM int)
AS
begin
declare @i int
set @i = 1
declare @t table(id int,BillId int, MGoodsId Varchar(10), MQty int, BomVer Varchar(10), ItemNo int, GoodsId Varchar(10), Qty int, BomId int, BomIdName Varchar(10), Price int, AM int, LevelId int)
insert @t
select a.BillId,a.BillId,a.GoodsId, a.Qty, a.BomVer, b.ItemNo, b.GoodsId, b.Qty, b.BomId, c.BOMVER AS BomIdName, d.Price, b.Qty*d.Price AS AM, @i
from M_BOM a
join M_BOMD b ON a.BillId = b.BillId
left join M_BOM c ON b.BomId = c.BillId
join GOODS d ON b.GoodsId = d.GoodsId
while @i <= 10 AND @@RowCount <> 0
begin
set @i = @i + 1
insert @t
select a.Id,b.BillId, a.BomIdName, a.Qty, a.BomIdName, b.ItemNo, b.GoodsId, b.Qty, b.BomId, c.BOMVER AS BomIdName, d.Price, b.Qty*d.Price AS AM, @i
from @t a
join M_BOMD b ON a.BomId = b.BillId
left join M_BOM c ON b.BomId = c.BillId
join GOODS d ON b.GoodsId = d.GoodsId
where a.BomIdName IS NOT NULL AND a.LevelId = @i-1
end
select @i = max(levelid) from @t
while @i >= 2
begin
update a set a.price = b.total, a.AM = a.Qty*b.total from @t a join (select id,BillId, sum(Qty*Price) as total from @t where levelid = @i group by id,BillId) b on a.BomId = b.BillId and a.id = b.id where a.levelid = @i-1
set @i = @i - 1
end
insert @tb(MGoodsId, MQty, BomVer, ItemNo, GoodsId, Qty, BomIdName, Price, AM)
select MGoodsId, MQty, BomVer, ItemNo, GoodsId, Qty, BomIdName, Price, AM
from @t
where levelid = 1
order by billid, itemno
return
end
go
select * from dbo.getbom() where mgoodsid = 'x'
------解决思路----------------------
递归只能向下，要把金额向上统计还是很麻烦。
DECLARE @tree TABLE (
ID uniqueidentifier,
PID uniqueidentifier,
RootID int,
Level int,
BOMVER varchar(10),
ItemNo int,
GoodsID varchar(10),
Qty int,
Price money,
Amount money
);
WITH M_BOM --单据ID,母件,数量,版本号
(BILLID,GOODSID,QTY,BOMVER) AS (
SELECT 1,'A',1,'A' UNION ALL
SELECT 2,'D',1,'D' UNION ALL
SELECT 3,'D',1,'D2' UNION ALL
SELECT 4,'A',1,'A2'
),
M_BOMD --单据ID,子件序号,子件,数量,关联主表BILLID
(BILLID,ITEMNO,GOODSID,QTY,BOMID) AS (
SELECT 1,1,'B',1,NULL UNION ALL
SELECT 1,2,'C',1,NULL UNION ALL
SELECT 1,3,'D',1,2 UNION ALL
SELECT 1,4,'D',1,2 UNION ALL
SELECT 2,1,'E',1,NULL UNION ALL
SELECT 2,2,'F',1,NULL UNION ALL
SELECT 3,1,'E',1,NULL UNION ALL
SELECT 3,2,'F',2,NULL UNION ALL
SELECT 4,1,'B',1,NULL UNION ALL
SELECT 4,2,'C',1,NULL UNION ALL
SELECT 4,3,'D',1,3
),
GOODS --货品,单价
(GOODSID,PRICE) AS (
SELECT 'A',Convert(money,1) UNION ALL
SELECT 'B',1 UNION ALL
SELECT 'C',1 UNION ALL
SELECT 'D',1 UNION ALL
SELECT 'E',1 UNION ALL
SELECT 'F',1
),
BOM_TREE AS (
SELECT NEWID() ID,
Convert(uniqueidentifier,NULL) AS PID,
M.BILLID RootID,
0 AS Level,
M.BILLID,
NULL BOMID,
M.BOMVER,
1 AS ItemNo,
M.GoodsID,
M.Qty,
G.Price,
M.Qty*G.Price AS Amount
FROM M_BOM M
JOIN GOODS G
ON G.GoodsID = M.GoodsID
WHERE NOT EXISTS (SELECT *
FROM M_BOMD
WHERE BOMID = M.BILLID)
UNION ALL
SELECT NEWID() ID,
T.ID AS PID,
T.RootID,
T.Level+1 AS Level,
NULL BILLID,
D.BOMID,
T.BOMVER,
D.ItemNo,
D.GoodsID,
T.Qty*D.Qty AS Qty,
G.Price,
T.Qty*D.Qty*G.Price AS Amount
FROM BOM_TREE T
JOIN M_BOMD D
ON D.BILLID = T.BILLID
JOIN GOODS G
ON G.GoodsID = D.GoodsID
UNION ALL
SELECT NEWID() ID,
T.ID AS PID,
T.RootID,
T.Level+1 AS Level,
M.BILLID,
NULL BOMID,
M.BOMVER,
1 AS ItemNo,
M.GoodsID,
T.Qty*M.Qty AS Qty,
G.Price,
T.Qty*M.Qty*G.Price AS Amount
FROM BOM_TREE T
JOIN M_BOM M
ON M.BILLID = T.BOMID
JOIN GOODS G
ON G.GoodsID = M.GoodsID
)
--SELECT * FROM BOM_TREE ORDER BY RootID,Level,PID,ItemNo
INSERT INTO @tree
SELECT ID,PID,RootID,Level,BOMVER,ItemNo,GoodsID,Qty,Price,Amount
FROM BOM_TREE
ORDER BY RootID,Level,PID,ItemNo
DECLARE @level int
SELECT @level = MAX(Level) FROM @tree
WHILE @level > 1
BEGIN
UPDATE @tree
SET t.Amount = s.Amount,
t.Price = s.Amount / t.Qty
FROM @tree t,
(
SELECT PID,
SUM(Amount) AS Amount
FROM @tree
WHERE level=@level
GROUP BY PID
) s
WHERE t.ID = s.PID
SET @level = @level-1
END
--SELECT * FROM @tree
SELECT t0.GoodsID MGoodsID,
t0.Qty MQty,
t0.BOMVER,
t1.ItemNo,
t1.GoodsID,
t1.Qty,
t2.BOMVER BOMIDNAME,
t1.Price,
t1.Amount
FROM @tree t0
JOIN @tree t1
ON t1.PID = t0.ID
LEFT JOIN @tree t2
ON t2.PID = t1.ID
WHERE t0.Level = 0
ORDER BY t1.BOMVER, t1.ItemNo
MGoodsID MQty BOMVER ItemNo GoodsID Qty BOMIDNAME Price Amount
---------- ----------- ---------- ----------- ---------- ----------- ---------- --------------------- ---------------------
A 1 A 1 B 1 NULL 1.00 1.00
A 1 A 2 C 1 NULL 1.00 1.00
A 1 A 3 D 1 D 2.00 2.00
A 1 A 4 D 1 D 2.00 2.00
A 1 A2 1 B 1 NULL 1.00 1.00
A 1 A2 2 C 1 NULL 1.00 1.00
A 1 A2 3 D 1 D2 3.00 3.00
------解决思路----------------------
比如你问：table1 表中 date 列为日期值，我要计算这些相邻日期间隔的分钟数。
我回复带模拟数据
WITH table1 (date) AS (
SELECT Convert(datetime,'2014-10-27 08:05:00',120) UNION ALL
SELECT Convert(datetime,'2014-10-27 12:59:00',120) UNION ALL
SELECT Convert(datetime,'2014-10-27 13:47:00',120)
)
,t AS (
SELECT date,
ROW_NUMBER() OVER(ORDER BY date) rn
FROM table1
)
SELECT t1.date date1,
t2.date date2,
DateDiff(minute,t1.date,t2.date) minutes
FROM t t1
JOIN t t2
ON t2.rn = t1.rn+1
你真实环境下就去掉模拟数据
WITH t AS (
SELECT date,
ROW_NUMBER() OVER(ORDER BY date) rn
FROM table1
)
SELECT t1.date date1,
t2.date date2,
DateDiff(minute,t1.date,t2.date) minutes
FROM t t1
JOIN t t2
ON t2.rn = t1.rn+1