求物料需求計算方法??? 需求計算比較復雜,要求物料在不同公司可以相互調用,在一定規則下兩種物料間可以替換使用
/*
求物料需求計算方法??? 需求計算比較復雜,要求物料在不同公司可以相互調用,在一定規則下兩種物料間可以替換使用
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TESTDATA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TESTDATA]
GO
CREATE TABLE [dbo].[TESTDATA] (
[S_ITEM_CODE] [varchar] (30) COLLATE Latin1_General_BIN NULL ,
[DB] [varchar] (10) COLLATE Latin1_General_BIN NULL ,
[ITEM_CODE] [varchar] (30) COLLATE Latin1_General_BIN NULL ,
[DATE_EXP] [datetime] NULL ,
[STOCK_QTYA] [int] NULL ,
[STOCK_QTYB] [int] NULL ,
[NET_QTY] [int] NULL
) ON [PRIMARY]
GO
資料為兩公司(MRP,MRPM)目前物料的進出數據表
求出各物料各日期的以下三個數據
1.MOVE (物料公司間的調用數,>0從別的公司調入,<0調出給別的公司)
2.INSTEAD (物料替換數,>0調用進來替換的數量,<0調用出用作替換的數量)
3.QTY (物料的需求數,要進貨數)
計算方法:
數據可按 物料編號 ASC,日期 ASC,數量 DESC 排序來計算
MRP,MRPM兩公司
物料記錄在各公司的日期時間點上如果缺貨
則檢查另外一公司在當前日期時間上是否有多出的數量可供調用,
如果能調用則要在兩公司發生調用的日期時間點上的記錄標示 MOVE 的數量
(數量的調用有多少可調就調用多少)
如果沒有或調用的數量不夠則檢查是否可以用別的物料來替換
如果有可用來替換的數量則要在發生替換的各物料日期點上的記錄標示 INSTEAD 的數量
當物料的缺貨數量不能通過,從別的公司調入數量或用來替換的數量,滿足時,則要在物料該記錄上標示需求數,要進貨數
經以上數量的調整後物料在記錄的日期時間點上的物料需求數據是平衡的(>=0)不會把缺貨數量帶入其後的日期中.
物料替換規則:
當前記錄的物料編號最後一位不是R則可用該物料編號+R的物料編號來替換
當前記錄的物料編號最後一位是R則檢查S_ITEM_CODE的最後一位是否是R,如果不是R則可以用去掉該物料編號最後一位(R)的物料編號來替換
替換時不分公司只要找可用來替換的物料在兩個公司的數據在這個日期時間上是否有數量可用來替換
例:
(當在該筆記錄的日期時間點上缺貨,同時在別的公司也調用不到足夠數量時)
000110042 可以用 000110042R 來替換
000110042R 當 S_ITEM_CODE=SF-SBK-1000-C310ER(最後一位是R) 則不可用 000110042 來替換
000110042R 當 S_ITEM_CODE=SF-SBK2000-C310E1KL(最後一位不是R) 則可用 000110042 來替換
*/
问题点数：100、回复次数：5Top
1 楼laker_tmj（laker）回复于 2006-03-04 12:52:57 得分 0
/*
測試數據:
INSERT TESTDATA VALUES('SF-SBK-1000-C310E','MRP','000110042','2005-03-07','60','0','-2')
INSERT TESTDATA VALUES('','MRPM','000110042','2005-03-08','5','0','-2')
INSERT TESTDATA VALUES('','MRPM','000110042','2005-03-08','5','0','-2')
INSERT TESTDATA VALUES('SF-SK30X-1000-C310E','MRP','000110042','2005-03-14','60','0','-1')
INSERT TESTDATA VALUES('','MRP','000110042','2005-03-16','60','0','-29')
INSERT TESTDATA VALUES('SF-AQ10-C3/10E','MRP','000110042','2005-05-02','60','0','-1')
INSERT TESTDATA VALUES('SF-SK30X-500-C310E','MRP','000110042','2005-05-16','60','0','-1')
INSERT TESTDATA VALUES('','MRP','000110042','2005-06-16','60','0','-12')
INSERT TESTDATA VALUES('','MRP','000110042','2005-06-16','60','0','-13')
INSERT TESTDATA VALUES('SF-SBK2000-C310E1KLR','MRPM','000110042','2006-04-10','5','0','-2')
INSERT TESTDATA VALUES('SF-SBK1000-C310E1KLR','MRPM','000110042','2006-04-13','5','0','-1')
INSERT TESTDATA VALUES('SF-AQ10-C3/10E','MRP','000110042R','2005-05-16','2705','0','-1')
INSERT TESTDATA VALUES('SF-AQ10-C3/10E','MRP','000110042R','2005-06-06','2705','0','-1')
INSERT TESTDATA VALUES('SF-AQ10-C3/10E','MRP','000110042R','2005-06-27','2705','0','-1')
INSERT TESTDATA VALUES('SF-AQ10-C3/10E','MRP','000110042R','2005-07-18','2705','0','-1')
INSERT TESTDATA VALUES('','MRP','000110042R','2005-09-01','2705','0','-19')
INSERT TESTDATA VALUES('','MRP','000110042R','2005-09-01','2705','0','-19')
INSERT TESTDATA VALUES('SF-AQ35-C310E','MRP','000110042R','2005-09-28','2705','0','-15')
INSERT TESTDATA VALUES('SF-SBK-1000-C310E','MRP','000110042R','2005-10-25','2705','0','-1')
INSERT TESTDATA VALUES('','MRPM','000110042R','2005-11-05','646','0','-1')
INSERT TESTDATA VALUES('','MRP','000110042R','2005-11-07','2705','0','-15')
INSERT TESTDATA VALUES('','MRPM','000110042R','2005-11-10','646','0','-1')
INSERT TESTDATA VALUES('SF-AHN250-C410E','MRP','000110042R','2005-11-28','2705','0','-1')
INSERT TESTDATA VALUES('','MRP','000110042R','2005-12-01','2705','0','0')
INSERT TESTDATA VALUES('SF-SBK-2000-C310E','MRP','000110042R','2005-12-02','2705','0','-1')
INSERT TESTDATA VALUES('SF-SBK-500-C310E','MRP','000110042R','2005-12-02','2705','0','-2')
INSERT TESTDATA VALUES('SF-SBK1000-C310E1KL','MRP','000110042R','2005-12-15','2705','0','-2')
INSERT TESTDATA VALUES('','MRPM','000110042R','2005-12-21','646','0','-1')
INSERT TESTDATA VALUES('','MRPM','000110042R','2005-12-21','646','0','-3')
INSERT TESTDATA VALUES('SF-SBK500-C310E1KL','MRP','000110042R','2005-12-22','2705','0','-2')
INSERT TESTDATA VALUES('','MRP','000110042R','2005-12-26','2705','0','-11')
*/Top
2 楼laker_tmj（laker）回复于 2006-03-04 12:53:13 得分 0
/*
INSERT TESTDATA VALUES('','MRP','000110042R','2005-12-26','2705','0','-15')
INSERT TESTDATA VALUES('SF-AQ35-C310ER','MRP','000110042R','2005-12-30','2705','0','-15')
INSERT TESTDATA VALUES('SF-SBK-2000-C310E','MRP','000110042R','2006-01-02','2705','0','-1')
INSERT TESTDATA VALUES('SF-SBK-1000-C310E','MRP','000110042R','2006-01-02','2705','0','-1')
INSERT TESTDATA VALUES('SF-SBK2000-C310E1KL','MRP','000110042R','2006-01-09','2705','0','-2')
INSERT TESTDATA VALUES('SF-SBK2000-C310E1KLR','MRP','000110042R','2006-01-12','2705','0','-2')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-01-13','646','0','-1')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-01-13','646','0','-2')
INSERT TESTDATA VALUES('SF-SBK-1000-C310ER','MRP','000110042R','2006-02-06','2705','0','-1')
INSERT TESTDATA VALUES('','MRP','000110042R','2006-02-09','2705','0','-2')
INSERT TESTDATA VALUES('','MRP','000110042R','2006-02-09','2705','0','-3')
INSERT TESTDATA VALUES('','MRP','000110042R','2006-02-09','2705','0','-229')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-02-10','646','0','-58')
INSERT TESTDATA VALUES('SF-AQ15-C3/10ER','MRP','000110042R','2006-02-13','2705','0','-1')
INSERT TESTDATA VALUES('SF-AS15-NEO-01R','MRP','000110042R','2006-02-13','2705','0','-4')
INSERT TESTDATA VALUES('SF-SBK2000-C310E1KLR','MRP','000110042R','2006-02-15','2705','0','-2')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-02-15','646','0','-2')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-02-15','646','0','-10')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-02-15','646','0','-10')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-02-16','646','0','-690')
INSERT TESTDATA VALUES('SF-AQ35-C310ER','MRP','000110042R','2006-02-17','2705','0','-15')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-02-20','646','0','-8')
INSERT TESTDATA VALUES('','MRP','000110042R','2006-02-20','2705','0','-154')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-02-24','646','0','-6')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-02-24','646','0','-6')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-02-24','646','0','-12')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-02-24','646','0','-12')
INSERT TESTDATA VALUES('SF-AS15-NEO-01R','MRP','000110042R','2006-02-27','2705','0','-4')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-02-27','646','0','-59')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-02-27','646','0','-59')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-03-01','646','0','-31')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-03-01','646','0','-31')
INSERT TESTDATA VALUES('','MRP','000110042R','2006-03-03','2705','0','-4')
INSERT TESTDATA VALUES('','MRP','000110042R','2006-03-03','2705','0','-4')
INSERT TESTDATA VALUES('','MRP','000110042R','2006-03-03','2705','0','-15')
INSERT TESTDATA VALUES('','MRP','000110042R','2006-03-03','2705','0','-15')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-03-04','646','0','-26')
*/Top
3 楼laker_tmj（laker）回复于 2006-03-04 12:53:38 得分 0
/*
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-03-04','646','0','-26')
INSERT TESTDATA VALUES('SF-SBK-1000-C310ER','MRP','000110042R','2006-03-06','2705','0','-1')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-03-08','646','0','-4')
INSERT TESTDATA VALUES('SF-AQ35-C310ER','MRP','000110042R','2006-03-10','2705','0','-15')
INSERT TESTDATA VALUES('','MRP','000110042R','2006-03-10','2705','0','-1300')
INSERT TESTDATA VALUES('','MRP','000110042R','2006-03-14','2705','0','1200')
INSERT TESTDATA VALUES('SF-SBK2000-C310E1KLR','MRP','000110042R','2006-03-15','2705','0','-2')
INSERT TESTDATA VALUES('SF-SBK-2000-C310ER','MRPM','000110042R','2006-03-17','646','0','-1')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-03-20','646','0','-2')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-03-20','646','0','-2')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-03-20','646','0','-2')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-03-20','646','0','-2')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-03-20','646','0','-2')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-03-20','646','0','-2')
INSERT TESTDATA VALUES('','MRPM','000110042R','2006-03-20','646','0','-2')
INSERT TESTDATA VALUES('SF-SBK-500-C310ER','MRPM','000110042R','2006-03-21','646','0','-2')
INSERT TESTDATA VALUES('SF-SBK-2000-C310ER','MRPM','000110042R','2006-03-24','646','0','-1')
INSERT TESTDATA VALUES('SF-SBK-300-C310ER','MRPM','000110042R','2006-03-24','646','0','-1')
INSERT TESTDATA VALUES('SF-AQ35-C310ER','MRPM','000110042R','2006-04-01','646','0','-15')
INSERT TESTDATA VALUES('SF-SBK-1000-C310ER','MRPM','000110042R','2006-04-07','646','0','-2')
INSERT TESTDATA VALUES('SF-SBK-500-C310ER','MRPM','000110042R','2006-04-07','646','0','-2')
INSERT TESTDATA VALUES('SF-AS15-NEO-01R','MRP','000110042R','2006-04-22','2705','0','-4')
INSERT TESTDATA VALUES('SF-SBK-1000-C310ER','MRPM','000110042R','2006-04-28','646','0','-2')
INSERT TESTDATA VALUES('SF-AS15-NEO-01R','MRP','000110042R','2006-06-22','2705','0','-4')
INSERT TESTDATA VALUES('FSM-61Z-01','MRP','700270307A0R','2005-12-21','0','0','-200')
INSERT TESTDATA VALUES('FSM-61-01','MRP','700270307A0R','2005-12-28','0','0','-200')
INSERT TESTDATA VALUES('FSM-61Z-03','MRP','700270307A0R','2005-12-28','0','0','-200')
INSERT TESTDATA VALUES('FSM-61Z-01','MRPM','700270307A0R','2006-03-04','60000','0','300000')
INSERT TESTDATA VALUES('FSM-61Z-01','MRPM','700270307A0R','2006-03-04','60000','0','-360000')
INSERT TESTDATA VALUES('FSM-61Z-02','MRP','700270307A0R','2006-03-05','0','0','200000')
INSERT TESTDATA VALUES('FSM-61Z-02','MRP','700270307A0R','2006-03-05','0','0','-200000')
INSERT TESTDATA VALUES('FSM-61Z-03','MRP','700270307A0R','2006-03-17','0','0','405600')
INSERT TESTDATA VALUES('FSM-61Z-03','MRP','700270307A0R','2006-03-17','0','0','-405600')
INSERT TESTDATA VALUES('FSM-61Z-01','MRPM','700270307A0R','2006-04-12','60000','0','425400')
INSERT TESTDATA VALUES('FSM-61Z-01','MRPM','700270307A0R','2006-04-12','60000','0','-425400')
INSERT TESTDATA VALUES('FSM-61-01','MRPM','700270307A0R','2006-04-14','60000','0','120000')
INSERT TESTDATA VALUES('FSM-61-01','MRPM','700270307A0R','2006-04-14','60000','0','-120000')
*/
--000110042,700270307A0RTop
4 楼scmail81（琳·风の狼(修罗)）回复于 2006-03-04 15:08:21 得分 0
你不如把你要的结果也写上!
看不懂你上面的需求!Top
5 楼znjgress（四空和尚）回复于 2006-03-06 20:42:16 得分 0
有个工具叫baidu 听说比较好用Top
