呵呵,晕了,写得太长,要分几个贴了.... declare @t table (no char(3),m char(2),qty int,wno int,bd datetime,ed datetime,maxqty int) insert into @t select 'A01','FG', 500, 10, '2000/1/20','2000/2/11', 50 union select 'A01','FG', 500, 20, '2000/1/20','2000/2/11', 30 union select 'A01','FG', 500, 30, '2000/1/20','2000/2/11', 40 select no,m,qty,wno,bd,ed,convert(char(7),ym,21) as ym,maxqty, case when datepart(dw,dateadd(day, 0,ym)) in (1,7) then '-' when dateadd(day, 0,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 0,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [1], case when datepart(dw,dateadd(day, 1,ym)) in (1,7) then '-' when dateadd(day, 1,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 1,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [2], case when datepart(dw,dateadd(day, 2,ym)) in (1,7) then '-' when dateadd(day, 2,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 2,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [3], case when datepart(dw,dateadd(day, 3,ym)) in (1,7) then '-' when dateadd(day, 3,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 3,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [4], case when datepart(dw,dateadd(day, 4,ym)) in (1,7) then '-' when dateadd(day, 4,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 4,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [5], case when datepart(dw,dateadd(day, 5,ym)) in (1,7) then '-' when dateadd(day, 5,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 5,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [6], case when datepart(dw,dateadd(day, 6,ym)) in (1,7) then '-' when dateadd(day, 6,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 6,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [7], case when datepart(dw,dateadd(day, 7,ym)) in (1,7) then '-' when dateadd(day, 7,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 7,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [8], case when datepart(dw,dateadd(day, 8,ym)) in (1,7) then '-' when dateadd(day, 8,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 8,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [9], case when datepart(dw,dateadd(day, 9,ym)) in (1,7) then '-' when dateadd(day, 9,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 9,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [10], case when datepart(dw,dateadd(day,10,ym)) in (1,7) then '-' when dateadd(day,10,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,10,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [11], case when datepart(dw,dateadd(day,11,ym)) in (1,7) then '-' when dateadd(day,11,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,11,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [12], case when datepart(dw,dateadd(day,12,ym)) in (1,7) then '-' when dateadd(day,12,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,12,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [13], case when datepart(dw,dateadd(day,13,ym)) in (1,7) then '-' when dateadd(day,13,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,13,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [14], case when datepart(dw,dateadd(day,14,ym)) in (1,7) then '-' when dateadd(day,14,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,14,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [15], case when datepart(dw,dateadd(day,15,ym)) in (1,7) then '-' when dateadd(day,15,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,15,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [16], case when datepart(dw,dateadd(day,16,ym)) in (1,7) then '-' when dateadd(day,16,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,16,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [17], case when datepart(dw,dateadd(day,17,ym)) in (1,7) then '-' when dateadd(day,17,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,17,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [18], case when datepart(dw,dateadd(day,18,ym)) in (1,7) then '-' when dateadd(day,18,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,18,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [19], case when datepart(dw,dateadd(day,19,ym)) in (1,7) then '-' when dateadd(day,19,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,19,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [20],
case when datepart(dw,dateadd(day,20,ym)) in (1,7) then '-' when dateadd(day,20,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,20,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [21], case when datepart(dw,dateadd(day,21,ym)) in (1,7) then '-' when dateadd(day,21,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,21,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [22], case when datepart(dw,dateadd(day,22,ym)) in (1,7) then '-' when dateadd(day,22,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,22,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [23], case when datepart(dw,dateadd(day,23,ym)) in (1,7) then '-' when dateadd(day,23,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,23,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [24], case when datepart(dw,dateadd(day,24,ym)) in (1,7) then '-' when dateadd(day,24,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,24,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [25], case when datepart(dw,dateadd(day,25,ym)) in (1,7) then '-' when dateadd(day,25,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,25,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [26], case when datepart(dw,dateadd(day,26,ym)) in (1,7) then '-' when dateadd(day,26,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,26,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [27], case when datepart(dw,dateadd(day,27,ym)) in (1,7) then '-' when dateadd(day,27,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,27,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [28], case when isdate(convert(char(8),ym,21)+'29')=0 then '×' when datepart(dw,dateadd(day,28,ym)) in (1,7) then '-' when dateadd(day,28,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,28,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [29], case when isdate(convert(char(8),ym,21)+'30')=0 then '×' when datepart(dw,dateadd(day,29,ym)) in (1,7) then '-' when dateadd(day,29,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,29,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [30], case when isdate(convert(char(8),ym,21)+'31')=0 then '×' when datepart(dw,dateadd(day,30,ym)) in (1,7) then '-' when dateadd(day,30,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,30,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [31] from ( select no,m,qty,wno,bd,ed,maxqty,ed_tmp,last_qty,dateadd(mm,g.rn-1,bd-datepart(day,bd)+1) as ym from ( select *,wds_2-1+bd as ed_tmp,datediff(month,bd,wds_2-1+bd)+1 as months, case qty%maxqty when 0 then maxqty else qty%maxqty end as last_qty from ( select *,wds+floor((datepart(dw,bd)+wds-2)/5)*2- case when datepart(dw,bd)=1 then 1 else 0 end- case (datepart(dw,bd)+wds-2)%5 when 0 then 2 when 1 then 1 else 0 end as wds_2 from ( select a.no,a.m,a.qty,a.wno,a.bd,a.ed,min(b.maxqty) as maxqty, ceiling(a.qty/cast(min(b.maxqty) as numeric(18,2))) as wds from @t a left join @t b on a.wno>=b.wno group by a.no,a.m,a.qty,a.wno,a.bd,a.ed ) c ) d ) e left join (select (select count(*) from sysobjects where id<=f.id) rn from sysobjects f) g on e.months>=g.rn ) h order by no,ym,wno
ceiling(a.qty/cast(min(b.maxqty) as numeric(18,2))) as wds 這句已經取整了,(倒數第10行) 為什麼case (datepart(dw,bd)+wds-2)%5 when 0 then 2 when 1 then 1 else 0 end as wds_2(倒數第13行) 報 Msg 8117, Level 16, State 1, Line 9 modulo 運算子的運算元資料型別 numeric 無效。 Msg 206, Level 16, State 2, Line 9 運算元型別衝突: int 與 void type 不相容
知道了,用ceiling取整後再cast int就可以了
llj0209013知道,你可以問他,他是高手
說說我的理解 wds 表示最長的有效工作日天數 wds_2 表示各工序總需要的天數(周末加工作日)
看了一天,才搞懂了那麼點東西ed_tmp,--每個工序具體的結束日期; last_qty--每個工序最後一天的生產量(取模); (select (select count(*) from sysobjects where id<=f.id) rn from sysobjects f) g on e.months>=g.rn----如果開始日期和結束日期跨月則拆分月份; datepart(dw,dateadd(day, 0,ym)) in (1,7)周末; dateadd(day, 0,ym)=ed_tmp 每個工序的結束日期 就分配最後一天的生產量last_qty; dateadd(day, 0,ym) between bd and ed_tmp開始日期到結束日期就安排最大生產量 maxqty
關於日期的處理,sdxiong有很好的方法。 但對其他同事來說,不太容易維護,所以我自己寫了函數,如獲取結束日期--根據開始日期、有效工作日,求出結束日期 --writen by hanjoe 2008-08-07 ALTER function [dbo].[Fun_GetEndDateByStartDateWorkDays] (@d1 datetime, @i int) returns datetime as begin declare @t_oped datetime declare @j int set @j=0 while(@j<@i) begin select @j=@j + case when datepart(dw,@d1) =1 or datepart(dw,@d1)=7 then 0 else 1 end set @d1=dateadd(day,1,@d1) end select @t_oped=dateadd(day,-1,@d1) return @t_oped end
訂單 物料 數量 工序 開始日期 結束日期 日最大產量
A01 FG 500 10 2000/1/20 2000/2/11 50
A01 FG 500 20 2000/1/20 2000/2/11 30
A01 FG 500 30 2000/1/20 2000/2/11 40
这个排程问题,
不是简单的几句SQL就可以实现的.
declare @t table (no char(3),m char(2),qty int,wno int,bd datetime,ed datetime,maxqty int)
insert into @t
select 'A01','FG', 500, 10, '2000/1/20','2000/2/11', 50 union
select 'A01','FG', 500, 20, '2000/1/20','2000/2/11', 30 union
select 'A01','FG', 500, 30, '2000/1/20','2000/2/11', 40 select no,m,qty,wno,bd,ed,convert(char(7),ym,21) as ym,maxqty,
case when datepart(dw,dateadd(day, 0,ym)) in (1,7) then '-' when dateadd(day, 0,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 0,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [1],
case when datepart(dw,dateadd(day, 1,ym)) in (1,7) then '-' when dateadd(day, 1,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 1,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [2],
case when datepart(dw,dateadd(day, 2,ym)) in (1,7) then '-' when dateadd(day, 2,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 2,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [3],
case when datepart(dw,dateadd(day, 3,ym)) in (1,7) then '-' when dateadd(day, 3,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 3,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [4],
case when datepart(dw,dateadd(day, 4,ym)) in (1,7) then '-' when dateadd(day, 4,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 4,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [5],
case when datepart(dw,dateadd(day, 5,ym)) in (1,7) then '-' when dateadd(day, 5,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 5,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [6],
case when datepart(dw,dateadd(day, 6,ym)) in (1,7) then '-' when dateadd(day, 6,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 6,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [7],
case when datepart(dw,dateadd(day, 7,ym)) in (1,7) then '-' when dateadd(day, 7,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 7,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [8],
case when datepart(dw,dateadd(day, 8,ym)) in (1,7) then '-' when dateadd(day, 8,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 8,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [9],
case when datepart(dw,dateadd(day, 9,ym)) in (1,7) then '-' when dateadd(day, 9,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day, 9,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [10], case when datepart(dw,dateadd(day,10,ym)) in (1,7) then '-' when dateadd(day,10,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,10,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [11],
case when datepart(dw,dateadd(day,11,ym)) in (1,7) then '-' when dateadd(day,11,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,11,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [12],
case when datepart(dw,dateadd(day,12,ym)) in (1,7) then '-' when dateadd(day,12,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,12,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [13],
case when datepart(dw,dateadd(day,13,ym)) in (1,7) then '-' when dateadd(day,13,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,13,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [14],
case when datepart(dw,dateadd(day,14,ym)) in (1,7) then '-' when dateadd(day,14,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,14,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [15],
case when datepart(dw,dateadd(day,15,ym)) in (1,7) then '-' when dateadd(day,15,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,15,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [16],
case when datepart(dw,dateadd(day,16,ym)) in (1,7) then '-' when dateadd(day,16,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,16,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [17],
case when datepart(dw,dateadd(day,17,ym)) in (1,7) then '-' when dateadd(day,17,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,17,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [18],
case when datepart(dw,dateadd(day,18,ym)) in (1,7) then '-' when dateadd(day,18,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,18,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [19],
case when datepart(dw,dateadd(day,19,ym)) in (1,7) then '-' when dateadd(day,19,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,19,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [20],
case when datepart(dw,dateadd(day,21,ym)) in (1,7) then '-' when dateadd(day,21,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,21,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [22],
case when datepart(dw,dateadd(day,22,ym)) in (1,7) then '-' when dateadd(day,22,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,22,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [23],
case when datepart(dw,dateadd(day,23,ym)) in (1,7) then '-' when dateadd(day,23,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,23,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [24],
case when datepart(dw,dateadd(day,24,ym)) in (1,7) then '-' when dateadd(day,24,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,24,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [25],
case when datepart(dw,dateadd(day,25,ym)) in (1,7) then '-' when dateadd(day,25,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,25,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [26],
case when datepart(dw,dateadd(day,26,ym)) in (1,7) then '-' when dateadd(day,26,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,26,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [27],
case when datepart(dw,dateadd(day,27,ym)) in (1,7) then '-' when dateadd(day,27,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,27,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [28], case when isdate(convert(char(8),ym,21)+'29')=0 then '×' when datepart(dw,dateadd(day,28,ym)) in (1,7) then '-' when dateadd(day,28,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,28,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [29],
case when isdate(convert(char(8),ym,21)+'30')=0 then '×' when datepart(dw,dateadd(day,29,ym)) in (1,7) then '-' when dateadd(day,29,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,29,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [30],
case when isdate(convert(char(8),ym,21)+'31')=0 then '×' when datepart(dw,dateadd(day,30,ym)) in (1,7) then '-' when dateadd(day,30,ym)=ed_tmp then cast(last_qty as char(5)) when dateadd(day,30,ym) between bd and ed_tmp then cast(maxqty as char(5)) else '' end as [31]
from (
select no,m,qty,wno,bd,ed,maxqty,ed_tmp,last_qty,dateadd(mm,g.rn-1,bd-datepart(day,bd)+1) as ym
from (
select *,wds_2-1+bd as ed_tmp,datediff(month,bd,wds_2-1+bd)+1 as months,
case qty%maxqty when 0 then maxqty else qty%maxqty end as last_qty
from (
select *,wds+floor((datepart(dw,bd)+wds-2)/5)*2-
case when datepart(dw,bd)=1 then 1 else 0 end-
case (datepart(dw,bd)+wds-2)%5 when 0 then 2 when 1 then 1 else 0 end as wds_2
from (
select a.no,a.m,a.qty,a.wno,a.bd,a.ed,min(b.maxqty) as maxqty,
ceiling(a.qty/cast(min(b.maxqty) as numeric(18,2))) as wds
from @t a
left join @t b on a.wno>=b.wno
group by a.no,a.m,a.qty,a.wno,a.bd,a.ed
) c
) d
) e
left join (select (select count(*) from sysobjects where id<=f.id) rn from sysobjects f) g on e.months>=g.rn
) h
order by no,ym,wno
no m qty wno bd ed ym maxqty 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
---- ---- ----------- ----------- ------------------------------------------------------ ------------------------------------------------------ ------- ----------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
A01 FG 500 10 2000-01-20 00:00:00.000 2000-02-11 00:00:00.000 2000-01 50 - - - - - - 50 50 - - 50 50 50 50 50 - - 50
A01 FG 500 20 2000-01-20 00:00:00.000 2000-02-11 00:00:00.000 2000-01 30 - - - - - - 30 30 - - 30 30 30 30 30 - - 30
A01 FG 500 30 2000-01-20 00:00:00.000 2000-02-11 00:00:00.000 2000-01 30 - - - - - - 30 30 - - 30 30 30 30 30 - - 30
A01 FG 500 10 2000-01-20 00:00:00.000 2000-02-11 00:00:00.000 2000-02 50 50 50 - - - - - - - - × ×
A01 FG 500 20 2000-01-20 00:00:00.000 2000-02-11 00:00:00.000 2000-02 30 30 30 30 30 - - 30 30 30 30 20 - - - - - - × ×
A01 FG 500 30 2000-01-20 00:00:00.000 2000-02-11 00:00:00.000 2000-02 30 30 30 30 30 - - 30 30 30 30 20 - - - - - - × ×(所影响的行数为 6 行)
我先試一下!
為什麼case (datepart(dw,bd)+wds-2)%5 when 0 then 2 when 1 then 1 else 0 end as wds_2(倒數第13行)
報 Msg 8117, Level 16, State 1, Line 9
modulo 運算子的運算元資料型別 numeric 無效。
Msg 206, Level 16, State 2, Line 9
運算元型別衝突: int 與 void type 不相容
wds 表示最長的有效工作日天數
wds_2 表示各工序總需要的天數(周末加工作日)
last_qty--每個工序最後一天的生產量(取模);
(select (select count(*) from sysobjects where id<=f.id) rn from sysobjects f) g
on e.months>=g.rn----如果開始日期和結束日期跨月則拆分月份;
datepart(dw,dateadd(day, 0,ym)) in (1,7)周末;
dateadd(day, 0,ym)=ed_tmp 每個工序的結束日期 就分配最後一天的生產量last_qty;
dateadd(day, 0,ym) between bd and ed_tmp開始日期到結束日期就安排最大生產量 maxqty
select 'A01','FG', 500, 10, '2000/1/20','2000/2/11', 600 union
select 'A01','FG', 500, 20, '2000/1/20','2000/2/11', 30 union
select 'A01','FG', 500, 30, '2000/1/20','2000/2/11', 40 弄明白sdxiong的思路了,呵呵
但對其他同事來說,不太容易維護,所以我自己寫了函數,如獲取結束日期--根據開始日期、有效工作日,求出結束日期
--writen by hanjoe 2008-08-07
ALTER function [dbo].[Fun_GetEndDateByStartDateWorkDays]
(@d1 datetime,
@i int)
returns datetime as
begin
declare @t_oped datetime
declare @j int
set @j=0
while(@j<@i)
begin
select @j=@j + case when datepart(dw,@d1) =1 or datepart(dw,@d1)=7 then 0 else 1 end
set @d1=dateadd(day,1,@d1)
end
select @t_oped=dateadd(day,-1,@d1)
return @t_oped
end