select a.id,a.item,a.leadtime,dateadd(day,-sum(b.ct)-1,c.riqi)
from 工序表 a left join 工序表 b on a.id<b.id
left join 订单表 c on a.item=c.item
group by a.id,a.item,a.leadtime
试试,每测
from 工序表 a left join 工序表 b on a.id<b.id
left join 订单表 c on a.item=c.item
group by a.id,a.item,a.leadtime
试试,每测
from 工序表 a left join 工序表 b on a.id<b.id
left join 订单表 c on a.item=c.item
group by a.id,a.item,a.leadtime,c.riqi
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1(id int, Item varchar(8), leadtime int)
insert into #1
select 1, 'A', 1 union all
select 2, 'A', 2 union all
select 3, 'A', 1
--> 测试数据:#2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2(Item varchar(8), riqi varchar(8))
insert into #2
select 'A', '8-5'select *, dateadd(day, a.a, ltrim(year(getdate()))+'-'+b.riqi) from
(
select *, a = (select -sum(leadtime) from #1 where item=t.item and id>=t.id) from #1 as t
) a
inner join #2 b on a.item=b.item
/*
id Item leadtime a Item riqi
----------- -------- ----------- ----------- -------- -------- -----------------------
1 A 1 -4 A 8-5 2010-08-01 00:00:00.000
2 A 2 -3 A 8-5 2010-08-02 00:00:00.000
3 A 1 -1 A 8-5 2010-08-04 00:00:00.000
*/
if not object_id('tb1') is null
drop table tb1
Goselect 1 id,'A' Item, 1 leadtime into tb1
union all select 2,'A', 2
union all select 3,'A', 1if not object_id('tb2') is null
drop table tb2
Go
;with tb as (
select *, use_time = (select sum(leadtime) from tb1 where item=t.item and id>=t.id) from tb1 as t)
select a.id,a.item,a.leadtime
,convert(char(5),dateadd(day,0-a.use_time,convert(datetime,ltrim(year(getdate()))+'-'+b.riqi)),110) startdate
from tb a join tb2 b on a.item = b.item /*
id item leadtime startdate
1 A 1 08-01
2 A 2 08-02
3 A 1 08-04
*/
if object_id('工序表') is not null drop table 工序表
go
create table 工序表(id int,Item varchar(10),leadtime int)
go
insert into 工序表 (id,Item,leadtime)
select 1,'A',1 UNION ALL
select 2,'A',2 UNION ALL
select 3,'A',1
GO
if object_id('订单表') is not null drop table 订单表
go
create table 订单表(Item varchar(10),riqi datetime)
go
insert into 订单表 (Item,riqi)
select 'A','2010-08-05'
go
SELECT D.Item,D.ID,D.leadtime,(D.RIQI-1-D.完工耗时) AS 工序完工时间
FROM
(select B.Item AS Item,A.ID AS ID,A.leadtime AS leadtime,完工耗时=(select isnull(sum(leadtime),0) from 工序表 C where A.item=C.item and A.id<C.id),B.riqi AS RIQI
from 订单表 B
left join 工序表 A on A.Item=B.Item)D
WHERE 1=1
go感觉给的例子有点问题 ,考虑交货期前一天需完成最后一道工序,完工日期的话应该是8月1日,8月3日,8月4日
from 工序表 a left join 工序表 b on a.id<b.id
left join 订单表 c on a.item=c.item
group by a.id,a.item,a.leadtime,c.riqi