create table tb(日期 datetime,已占用 int,空间能力 int,剩余 int,编号 int) insert tb select '20110701', 100, 200 ,100 ,1 union all select '20110702', 0 ,200 ,200 ,2 union all select '20110703', 200 ,200 ,0 ,3 union all select '20110704', 200 ,200 ,0 ,4update tb set 已占用=已占用-100 where 日期>='20110703'insert tb select dateadd(dd,1,日期),100,空间能力,剩余,编号 from tb where 日期>='20110703' union all select '20110703',200,100,0,5select * from tb order by 日期,编号 drop table tb /* 日期 已占用 空间能力 剩余 编号 ------------------------------------------------------ ----------- ----------- ----------- ----------- 2011-07-01 00:00:00.000 100 200 100 1 2011-07-02 00:00:00.000 0 200 200 2 2011-07-03 00:00:00.000 100 200 0 3 2011-07-03 00:00:00.000 200 100 0 5 2011-07-04 00:00:00.000 100 200 0 3 2011-07-04 00:00:00.000 100 200 0 4 2011-07-05 00:00:00.000 100 200 0 4(所影响的行数为 7 行) */
--> 测试数据: #t if object_id('tempdb.dbo.#t') is not null drop table #t create table #t (TaskID int,Name varchar(1) ,RequiredCapility decimal(18,2) ,PriorityGrade int --排期顺序 ,IsSetUp bit null ) insert into #t (TaskID ,Name ,RequiredCapility ,PriorityGrade ) select 1,'A',500.00,1 union all select 2,'B',300.00,2 union all select 3,'C',100.00,3 union all select 4,'D',100.00,4 union all select 5,'E',200.00,6 union all select 6,'F',400.00,5 union all select 7,'G',350.00,8select * from #t--> 测试数据: #t1 if object_id('tempdb.dbo.#t1') is not null drop table #t1 create table #t1 (CapilityID int ,ProductDate datetime ,Capility decimal(18,2) ,IsCapilityOver bit null ) insert into #t1(CapilityID ,ProductDate ,Capility ) select 1,'2011-07-01',200.00 union all select 2,'2011-07-02',200.00 union all select 3,'2011-07-03',200.00 union all select 4,'2011-07-04',200.00 union all select 5,'2011-07-05',200.00 union all select 6,'2011-07-06',200.00 union all select 7,'2011-07-08',200.00 union all select 8,'2011-07-09',250.00 union all select 9,'2011-07-10',250.00 union all select 10,'2011-07-11',250.00 union all select 11,'2011-07-12',250.00select * from #t1--> 测试数据: #t2 if object_id('tempdb.dbo.#t2') is not null drop table #t2 create table #t2 (ScheduleID int identity(1,1),TaskID int,CapilityID int,Quantity decimal(18,2)) declare @restRows as int declare @restCapility as decimal(18,2) declare @restTask as decimal(18,2) declare @taskID INT DECLARE @CapilityID INTselect @restRows =COUNT(1) from #t while @restRows >0 begin select Capility ,ProductDate ,TaskID ,CapilityID ,case when Capility >RequiredCapility then RequiredCapility else Capility end quantity ,case when Capility >RequiredCapility then Capility -RequiredCapility else 0 end restCapility ,case when Capility >RequiredCapility then 0 else RequiredCapility -Capility end restRequireCapility into #t3 from (SELECT TOP 1 * FROM #T where IsSetUp is null ORDER BY PriorityGrade ) a ,(select top 1 * from #t1 where IsCapilityOver is null order by ProductDate) b
insert #t2 (TaskID ,CapilityID ,Quantity ) select TaskID ,CapilityID ,Quantity from #t3 if @restCapility =0 begin update #t1 set IsCapilityOver =1 where CapilityID = @CapilityID
update #t set RequiredCapility =@restTask where TaskID =@taskID end
if @restTask =0 begin update #t set IsSetUp =1 where TaskID =@taskID update #t1 set Capility =@restCapility where Capility =@CapilityID select @restRows =COUNT(1) from #t where IsSetUp is null end
--select * from #t3 drop table #t3
end
select * from #t2 a join #t b on a.TaskID =b.TaskID join #t1 c on a.CapilityID =c.CapilityID
感谢楼上的朋友 lhblxm 我去测试一下. 结果是正确的...
还有一行没看明白 if @restTask =0 begin update #t set IsSetUp =1 where TaskID =@taskID update #t1 set Capility =@restCapility where Capility =@CapilityID select @restRows =COUNT(1) from #t where IsSetUp is null end
其中 where Capility =@CapilityID 这2个是相等的吗?
搞错 where Capility =@CapilityID,应该是CapilityID =@CapilityID
lhblxm 朋友帮忙看一下,我把它弄成中文了,意思是不是这样的? 但结果却跟你的不一样,看是哪里理解错了?if object_id('tempdb.dbo.#t') is not null drop table #t create table #t (订单编号 int,产品类别 varchar(1) ,订单数量 decimal(18,2) ,生产编号 int --排期顺序 ,完成标识 bit null ) insert into #t (订单编号 ,产品类别 ,订单数量 ,生产编号 ) select 1,'A',500.00,1 union all select 2,'B',300.00,2 union all select 3,'C',100.00,3 union all select 4,'D',100.00,4 union all select 5,'E',200.00,6 union all select 6,'F',400.00,5 union all select 7,'G',350.00,8select * from #t--> 测试数据: #t1 if object_id('tempdb.dbo.#t1') is not null drop table #t1 create table #t1 (产能编号 int ,产能日期 datetime ,生产能力 decimal(18,2) ,被占用 bit null ) insert into #t1(产能编号 ,产能日期 ,生产能力 ) select 1,'2011-07-01',200.00 union all select 2,'2011-07-02',200.00 union all select 3,'2011-07-03',200.00 union all select 4,'2011-07-04',200.00 union all select 5,'2011-07-05',200.00 union all select 6,'2011-07-06',200.00 union all select 7,'2011-07-08',200.00 union all select 8,'2011-07-09',250.00 union all select 9,'2011-07-10',250.00 union all select 10,'2011-07-11',250.00 union all select 11,'2011-07-12',250.00select * from #t1--> 测试数据: #t2 if object_id('tempdb.dbo.#t2') is not null drop table #t2 create table #t2 (ScheduleID int identity(1,1),订单编号 int,产能编号 int,计划数量 decimal(18,2)) declare @restRows as int declare @restCapility as decimal(18,2) declare @restTask as decimal(18,2) declare @订单编号 INT DECLARE @产能编号 INTselect @restRows =COUNT(1) from #t while @restRows >0 begin select 生产能力 ,产能日期 ,订单编号 ,产能编号 ,case when 生产能力 >订单数量 then 订单数量 else 生产能力 end 计划数量 ,case when 生产能力 >订单数量 then 生产能力 -订单数量 else 0 end 重排生产能力 ,case when 生产能力 >订单数量 then 0 else 订单数量 -生产能力 end 重排计划 into #t3 from (SELECT TOP 1 * FROM #T where 完成标识 is null ORDER BY 生产编号 ) a ,(select top 1 * from #t1 where 被占用 is null order by 产能日期) b
insert #t2 (订单编号 ,产能编号 ,计划数量 ) select 订单编号 ,产能编号 ,计划数量 from #t3 if @restCapility =0----如果订单数量大于生产能力 begin update #t1 set 被占用 =1 where 产能编号 = @产能编号
update #t set 订单数量 =@restTask where 订单编号 =@订单编号 end
if @restTask =0 ---如果重新计划数量为0 begin update #t set 完成标识 =1 where 订单编号 =@订单编号 update #t1 set 生产能力 =@restCapility where 产能编号 =@产能编号 select @restRows =COUNT(1) from #t where 完成标识 is null end
--select * from #t3 drop table #t3
end
select * from #t2 a join #t b on a.订单编号 =b.订单编号 join #t1 c on a.产能编号 =c.产能编号
1:按客户的交期为完成时间,倒推出首工序的开始生产时间,
2:从当前时间开始生产,计算出最早的完成时间
按那种方式排应由参数定义,如果从交期倒推,最早的开始生产时间小于当前时间,则表明无法按期交货,
需要用第2种方式再排一次,计算出需要推迟多长时间交货用游标处理,里面有太多的计算与比较.不是一句sql可以解决的.
create table tb(日期 datetime,已占用 int,空间能力 int,剩余 int,编号 int)
insert tb
select '20110701', 100, 200 ,100 ,1 union all
select '20110702', 0 ,200 ,200 ,2 union all
select '20110703', 200 ,200 ,0 ,3 union all
select '20110704', 200 ,200 ,0 ,4update tb set 已占用=已占用-100 where 日期>='20110703'insert tb
select dateadd(dd,1,日期),100,空间能力,剩余,编号
from tb
where 日期>='20110703'
union all
select '20110703',200,100,0,5select * from tb order by 日期,编号
drop table tb
/*
日期 已占用 空间能力 剩余 编号
------------------------------------------------------ ----------- ----------- ----------- -----------
2011-07-01 00:00:00.000 100 200 100 1
2011-07-02 00:00:00.000 0 200 200 2
2011-07-03 00:00:00.000 100 200 0 3
2011-07-03 00:00:00.000 200 100 0 5
2011-07-04 00:00:00.000 100 200 0 3
2011-07-04 00:00:00.000 100 200 0 4
2011-07-05 00:00:00.000 100 200 0 4(所影响的行数为 7 行)
*/
--> 测试数据: #t
if object_id('tempdb.dbo.#t') is not null drop table #t
create table #t (TaskID int,Name varchar(1)
,RequiredCapility decimal(18,2)
,PriorityGrade int --排期顺序
,IsSetUp bit null
)
insert into #t (TaskID ,Name ,RequiredCapility ,PriorityGrade )
select 1,'A',500.00,1 union all
select 2,'B',300.00,2 union all
select 3,'C',100.00,3 union all
select 4,'D',100.00,4 union all
select 5,'E',200.00,6 union all
select 6,'F',400.00,5 union all
select 7,'G',350.00,8select * from #t--> 测试数据: #t1
if object_id('tempdb.dbo.#t1') is not null drop table #t1
create table #t1 (CapilityID int
,ProductDate datetime
,Capility decimal(18,2)
,IsCapilityOver bit null
)
insert into #t1(CapilityID ,ProductDate ,Capility )
select 1,'2011-07-01',200.00 union all
select 2,'2011-07-02',200.00 union all
select 3,'2011-07-03',200.00 union all
select 4,'2011-07-04',200.00 union all
select 5,'2011-07-05',200.00 union all
select 6,'2011-07-06',200.00 union all
select 7,'2011-07-08',200.00 union all
select 8,'2011-07-09',250.00 union all
select 9,'2011-07-10',250.00 union all
select 10,'2011-07-11',250.00 union all
select 11,'2011-07-12',250.00select * from #t1--> 测试数据: #t2
if object_id('tempdb.dbo.#t2') is not null drop table #t2
create table #t2 (ScheduleID int identity(1,1),TaskID int,CapilityID int,Quantity decimal(18,2))
declare @restRows as int
declare @restCapility as decimal(18,2)
declare @restTask as decimal(18,2)
declare @taskID INT
DECLARE @CapilityID INTselect @restRows =COUNT(1) from #t
while @restRows >0
begin
select Capility
,ProductDate
,TaskID
,CapilityID
,case when Capility >RequiredCapility then RequiredCapility
else Capility end quantity
,case when Capility >RequiredCapility then Capility -RequiredCapility
else 0 end restCapility
,case when Capility >RequiredCapility then 0
else RequiredCapility -Capility end restRequireCapility into #t3 from
(SELECT TOP 1 * FROM #T where IsSetUp is null ORDER BY PriorityGrade ) a
,(select top 1 * from #t1 where IsCapilityOver is null order by ProductDate) b
select @taskID =taskID
,@CapilityID =CapilityID
,@restCapility =RestCapility
,@restTask = restRequireCapility
from #t3
insert #t2 (TaskID ,CapilityID ,Quantity ) select TaskID ,CapilityID ,Quantity from #t3
if @restCapility =0
begin
update #t1 set IsCapilityOver =1 where CapilityID = @CapilityID
update #t set RequiredCapility =@restTask where TaskID =@taskID
end
if @restTask =0
begin
update #t set IsSetUp =1 where TaskID =@taskID
update #t1 set Capility =@restCapility where Capility =@CapilityID
select @restRows =COUNT(1) from #t where IsSetUp is null
end
--select * from #t3
drop table #t3
end
select * from #t2 a join #t b on a.TaskID =b.TaskID
join #t1 c on a.CapilityID =c.CapilityID
我去测试一下.
结果是正确的...
if @restTask =0
begin
update #t set IsSetUp =1 where TaskID =@taskID
update #t1 set Capility =@restCapility where Capility =@CapilityID select @restRows =COUNT(1) from #t where IsSetUp is null
end
其中 where Capility =@CapilityID 这2个是相等的吗?
where Capility =@CapilityID,应该是CapilityID =@CapilityID
朋友帮忙看一下,我把它弄成中文了,意思是不是这样的?
但结果却跟你的不一样,看是哪里理解错了?if object_id('tempdb.dbo.#t') is not null drop table #t
create table #t (订单编号 int,产品类别 varchar(1)
,订单数量 decimal(18,2)
,生产编号 int --排期顺序
,完成标识 bit null
)
insert into #t (订单编号 ,产品类别 ,订单数量 ,生产编号 )
select 1,'A',500.00,1 union all
select 2,'B',300.00,2 union all
select 3,'C',100.00,3 union all
select 4,'D',100.00,4 union all
select 5,'E',200.00,6 union all
select 6,'F',400.00,5 union all
select 7,'G',350.00,8select * from #t--> 测试数据: #t1
if object_id('tempdb.dbo.#t1') is not null drop table #t1
create table #t1 (产能编号 int
,产能日期 datetime
,生产能力 decimal(18,2)
,被占用 bit null
)
insert into #t1(产能编号 ,产能日期 ,生产能力 )
select 1,'2011-07-01',200.00 union all
select 2,'2011-07-02',200.00 union all
select 3,'2011-07-03',200.00 union all
select 4,'2011-07-04',200.00 union all
select 5,'2011-07-05',200.00 union all
select 6,'2011-07-06',200.00 union all
select 7,'2011-07-08',200.00 union all
select 8,'2011-07-09',250.00 union all
select 9,'2011-07-10',250.00 union all
select 10,'2011-07-11',250.00 union all
select 11,'2011-07-12',250.00select * from #t1--> 测试数据: #t2
if object_id('tempdb.dbo.#t2') is not null drop table #t2
create table #t2 (ScheduleID int identity(1,1),订单编号 int,产能编号 int,计划数量 decimal(18,2))
declare @restRows as int
declare @restCapility as decimal(18,2)
declare @restTask as decimal(18,2)
declare @订单编号 INT
DECLARE @产能编号 INTselect @restRows =COUNT(1) from #t
while @restRows >0
begin
select 生产能力
,产能日期
,订单编号
,产能编号
,case when 生产能力 >订单数量 then 订单数量
else 生产能力 end 计划数量
,case when 生产能力 >订单数量 then 生产能力 -订单数量
else 0 end 重排生产能力
,case when 生产能力 >订单数量 then 0
else 订单数量 -生产能力 end 重排计划 into #t3 from
(SELECT TOP 1 * FROM #T where 完成标识 is null ORDER BY 生产编号 ) a
,(select top 1 * from #t1 where 被占用 is null order by 产能日期) b
select @订单编号 =订单编号
,@产能编号 =产能编号
,@restCapility =重排生产能力
,@restTask = 重排计划
from #t3
insert #t2 (订单编号 ,产能编号 ,计划数量 ) select 订单编号 ,产能编号 ,计划数量 from #t3
if @restCapility =0----如果订单数量大于生产能力
begin
update #t1 set 被占用 =1 where 产能编号 = @产能编号
update #t set 订单数量 =@restTask where 订单编号 =@订单编号
end
if @restTask =0 ---如果重新计划数量为0
begin
update #t set 完成标识 =1 where 订单编号 =@订单编号
update #t1 set 生产能力 =@restCapility where 产能编号 =@产能编号
select @restRows =COUNT(1) from #t where 完成标识 is null
end
--select * from #t3
drop table #t3
end
select * from #t2 a join #t b on a.订单编号 =b.订单编号
join #t1 c on a.产能编号 =c.产能编号
比如客户交期为2011-08-01,
共a,b,c,d 4道工序,a工序生产要5天,
b工序生产要4天,c工序生产要6天,d工序生产要3天,
则d工序的完成时间为2011-08-01,生产要3天,开始时间为:2011-07-29
则c工序的完成时间为:2011-07-29,c工序生产要6天,则开始时间为:2011-07-23,
则B工序的完成时间为:2011-07-23,b工序生产要4天,则开始时间为:2011-07-19,
则A工序的完成时间为:2011-07-19,A工序生产要5天,则开始时间为:2011-07-14,即倒排的话,2011-07-14开始生产,可以满足客户交期,如果倒排首工序的开始时间在当前时间以前,即已不能按期交货,需要推迟交货,
那再顺排,如上例,如a工序需要15天的话,则倒排开始日期为2011-07-04,
已小于当前日期,那就再要从2011-07-07开始顺排,最后工序结束日期为2011-08-04,
即要推迟交货期到2011-08-04
按先倒排再顺排,想在sql里实现一个公共存储过程.