前面我已經發了個帖子,非常感謝 子陌红尘!
http://topic.csdn.net/u/20090715/11/acef40d0-9b64-414c-ba9e-de24e42e9864.html
隻是在不同物料佔用同一設備,日期重疊的時候我想不到辦法,所以隻好又來求教了。
--設備A每天的有效時間
declare @t table(Machine char(1) ,d datetime,Minutes int)
insert into @t
select 'A','2009-06-29',600 union all
select 'A','2009-06-30',600 union all
select 'A','2009-07-01',600 union all
select 'A','2009-07-02',400 union all
select 'A','2009-07-03',600 union all
select 'A','2009-07-04',700declare @v table(Item char(1),Machine char(1) ,d datetime,Minutes int)
insert into @v
select 'K','A','2009-06-29',1000 union all--物料K從29號開始,共需要1000時間
select 'D','A','2009-06-30',1300 --物料D從30號開始,共需要1300時間--求每天各物料佔用設備的時間,結果如下:
Item Machine d Minutes
K A 2009-06-29 600
K A 2009-06-30 400
D A 2009-06-30 200
D A 2009-07-01 600
D A 2009-07-02 600
D A 2009-07-03 100
http://topic.csdn.net/u/20090715/11/acef40d0-9b64-414c-ba9e-de24e42e9864.html
隻是在不同物料佔用同一設備,日期重疊的時候我想不到辦法,所以隻好又來求教了。
--設備A每天的有效時間
declare @t table(Machine char(1) ,d datetime,Minutes int)
insert into @t
select 'A','2009-06-29',600 union all
select 'A','2009-06-30',600 union all
select 'A','2009-07-01',600 union all
select 'A','2009-07-02',400 union all
select 'A','2009-07-03',600 union all
select 'A','2009-07-04',700declare @v table(Item char(1),Machine char(1) ,d datetime,Minutes int)
insert into @v
select 'K','A','2009-06-29',1000 union all--物料K從29號開始,共需要1000時間
select 'D','A','2009-06-30',1300 --物料D從30號開始,共需要1300時間--求每天各物料佔用設備的時間,結果如下:
Item Machine d Minutes
K A 2009-06-29 600
K A 2009-06-30 400
D A 2009-06-30 200
D A 2009-07-01 600
D A 2009-07-02 600
D A 2009-07-03 100
http://topic.csdn.net/u/20090224/21/9b4c9a94-9a7b-4360-adb6-ae4db51c8cbd.html
declare @t table(Machine varchar(10) ,d datetime,Minutes int)
insert into @t
select 'A','2009-06-29',600 union all
select 'A','2009-06-30',600 union all
select 'A','2009-07-01',600 union all
select 'A','2009-07-02',400 union all
select 'A','2009-07-03',600 union all
select 'A','2009-07-04',700declare @v table(Item varchar(10),Machine varchar(10),d datetime,Minutes int)
insert into @v
select 'K','A','2009-06-29',1000 union all --物料K從29號開始,共需要1000時間
select 'D','A','2009-06-30',1300 --物料D從30號開始,共需要1300時間select
b.Item,a.Machine,a.d,
case
when (a.num between b.num and b.num+b.Minutes)
and (a.Minutes+a.num between b.num and b.num+b.Minutes)
then a.Minutes
when (a.num between b.num and b.num+b.Minutes)
then b.Minutes+b.num - a.num
when (a.Minutes+a.num between b.num and b.num+b.Minutes)
then a.Minutes+a.num - b.num
end as Minutes
from
(select t.*,isnull((select sum(Minutes) from @t where Machine=t.Machine and d<t.d),0) as num from @t t) a,
(select t.*,isnull((select sum(Minutes) from @v where Machine=t.Machine and d<t.d),0) as num from @v t) b
where
a.Machine=b.Machine and a.d>=b.d
and
((a.num between b.num and b.num+b.Minutes)
or
(a.Minutes+a.num between b.num and b.num+b.Minutes))
order by
b.d,b.Item,a.d/*Item Machine d Minutes
---------- ---------- ------------------------------------------------------ -----------
K A 2009-06-29 00:00:00.000 600
K A 2009-06-30 00:00:00.000 400
D A 2009-06-30 00:00:00.000 200
D A 2009-07-01 00:00:00.000 600
D A 2009-07-02 00:00:00.000 400
D A 2009-07-03 00:00:00.000 100
*/
--設備A每天的有效時間
declare @t table(Machine varchar(10) ,d datetime,Minutes int)
insert into @t
select 'A','2009-06-29',600 union all
select 'A','2009-06-30',600 union all
select 'A','2009-07-01',600 union all
select 'A','2009-07-02',400 union all
select 'A','2009-07-03',600 union all
select 'A','2009-07-04',700 union all
select 'B','2009-06-29',600 union all
select 'B','2009-06-30',600 union all
select 'B','2009-07-01',600 union all
select 'B','2009-07-02',400 union all
select 'B','2009-07-03',600 union all
select 'B','2009-07-04',700declare @v table(Item varchar(10),Machine varchar(10),d datetime,Minutes int)
insert into @v
select 'K','A','2009-06-29',1000 union all
select 'D','A','2009-06-30',1300 union all
select 'K','B','2009-06-29',1700 union all
select 'R','B','2009-06-30',100 select
*
from
(select
b.Item,a.Machine,a.d,
case
when (a.num between b.num and b.num+b.Minutes)
and (a.Minutes+a.num between b.num and b.num+b.Minutes)
then a.Minutes
when (a.num between b.num and b.num+b.Minutes)
then b.Minutes+b.num - a.num
when (a.Minutes+a.num between b.num and b.num+b.Minutes)
then a.Minutes+a.num - b.num
else
b.Minutes
end as Minutes
from
(select t.*,isnull((select sum(Minutes) from @t where Machine=t.Machine and d<t.d),0) as num from @t t) a,
(select t.*,isnull((select sum(Minutes) from @v where Machine=t.Machine and d<t.d),0) as num from @v t) b
where
a.Machine=b.Machine and a.d>=b.d
and
((a.num between b.num and b.num+b.Minutes)
or
(a.Minutes+a.num between b.num and b.num+b.Minutes)
or
(b.num between a.num and a.num+a.Minutes)
or
((b.Minutes+b.num) between a.num and a.num+a.Minutes))) n
where
Minutes>0
order by
Machine,item,d/*
Item Machine d Minutes
---------- ---------- ------------------------------------------------------ -----------
D A 2009-06-30 00:00:00.000 200
D A 2009-07-01 00:00:00.000 600
D A 2009-07-02 00:00:00.000 400
D A 2009-07-03 00:00:00.000 100
K A 2009-06-29 00:00:00.000 600
K A 2009-06-30 00:00:00.000 400
K B 2009-06-29 00:00:00.000 600
K B 2009-06-30 00:00:00.000 600
K B 2009-07-01 00:00:00.000 500
R B 2009-07-01 00:00:00.000 100
*/
(select t.*,isnull((select sum(Minutes) from @t where Machine=t.Machine and d<t.d),0) as num from @t t) a,
--各物料前一天累計佔用同一設備總時間
(select t.*,isnull((select sum(Minutes) from @v where Machine=t.Machine and d<t.d),0) as num from @v t) b
--即:前一天累計的有效時間足夠前一天及以前佔用,但又不滿足當天佔用
when (a.num between b.num and b.num+b.Minutes)
--當天的累計有效時間 between 前一天累計佔用時間 and 當天累計佔用時間
--當天的累計有效時間當然大於前一天累計有效時間,但仍滿足不了當天需佔用的時間
and (a.Minutes+a.num between b.num and b.num+b.Minutes)
then a.Minutes--因此,當天的有效時間全部被佔用
沒有沒有,是您辛苦了!--前一天累計有效時間 between 前一天累計佔用時間 and 當天累計佔用時間
--前一步已經考慮了“當天的累計有效時間當然大於前一天累計有效時間,但仍滿足不了當天需佔用的時間”的情況
--因此這裡是前一天累計有效時間可以滿足當天佔用的
when (a.num between b.num and b.num+b.Minutes)
then b.Minutes+b.num - a.num--因此等於當天累計佔用時間減去前一天累計有效時間,
--前2步考慮前一天累計有效時間,這裡考慮當天累計有效時間
--當天累計有效時間 between 前一天累計佔用時間 and 當天累計佔用時間
--即當天有效時間不能滿足當天佔用時間
when (a.Minutes+a.num between b.num and b.num+b.Minutes)
then a.Minutes+a.num - b.num--因此,可用於分配的時間是:當天累計有效時間減去前一天累計佔用時間
这种逻辑主要用这几种处理模式:
1、游标
2、临时表
3、临时表+UPDATE模拟游标
4、集合处理(SELECT )