按照设定顺序的工序周期获取对应的时间

[yjkarmon]

各位老师，本人遇到一难题：根据某一个工序时间节点来反推排在这个工序前面的所有工序完工时间，为便于说明，我举例如下：
工序表:
declare @a table
(
[序号] int,
[工序] varchar(32),
[工序周期] decimal(3,1)
)

insert into @a
select 1,'A',3
union all
select 2, 'B',3.5
union all
select 3, 'C',4.5
union all
select 4, 'D',4
union all
select 5, 'E',4
union all
select 6, 'F',5

SELECT * FROM @A
序号 工序 工序周期
----------- -------------------------------- -----
1 A 3.0
2 B 3.5
3 C 4.5
4 D 4.0
5 E 4.0
6 F 5.0

--工序的工作日历
declare @b table
(
[开始时间] smalldatetime,
[结束时间] smalldatetime
)
declare @i int,
@date smalldatetime

set @date='2019-03-01'
set @i = 1
while @i<=20
begin
insert into @b
select DATEADD(mi,0,DATEADD(hh,8,@date)), DATEADD(mi,0,DATEADD(hh,12,@date))
union all select DATEADD(mi,30,DATEADD(hh,13,@date)),DATEADD(mi,30,DATEADD(hh,17,@date))
union all select DATEADD(mi,30,DATEADD(hh,18,@date)),DATEADD(mi,30,DATEADD(hh,20,@date))
set @i = @i +1
set @date=DATEADD(dd,1,@date)
end
select * from @b

给定一个完工时间：@date='2019-03-16 17:22:00'

求表@a里所有工序的周期算出工序的完工时间
工序F要完工的时间：2019-03-16 10:52:00.000(减去F工序的周期5小时)
工序E要完工的时间：2019-03-15 19:22:00.000(减去E工序的周期4小时,往前推一天)
工序D要完工的时间：2019-03-15 14:22:00.000(减去D工序的周期4小时)
工序C要完工的时间：2019-03-15 08:16:00.000(减去C工序的周期4.5小时)
其余工序如此类推....
谢谢各位老师解答。

[yjkarmon]

自已顶一下.

[yjkarmon]

自已再顶一下。

[Hello World,]

区间给你算出来，要什么自己改吧：

--要求完成时间
DECLARE @enddate DATETIME='2019-03-16 17:22:00'

DECLARE @seq INT,		--工序序号
@process VARCHAR(32),	--工序
@cycle DECIMAL(5,1),	--工序时间
@span INT,				--区间分钟数
@flag BIT				--是否分配
--定义一个表来存储时段
DECLARE @t TABLE(StartTime DATETIME NOT NULL, EndTime DATETIME NOT NULL, [Minutes] INT NOT NULL,flag BIT NULL DEFAULT(0),
processStartTime DATETIME NULL, processEndTime DATETIME NULL)
INSERT @t(StartTime, EndTime, [Minutes])
    SELECT 开始时间, CASE when 结束时间>@enddate THEN @enddate ELSE 结束时间 END 结束时间, 
	DATEDIFF(MINUTE,开始时间,CASE when 结束时间>@enddate THEN @enddate ELSE 结束时间 END) 分钟 
	FROM @b WHERE 结束时间<=@enddate OR @enddate BETWEEN 开始时间 AND 结束时间
	ORDER BY 开始时间 DESC

--SELECT * FROM @t
--输出的表
DECLARE @processTime TABLE(process VARCHAR(32) NOT NULL,StartTime datetime NOT NULL,EndTime DATETIME NOT NULL)

DECLARE cur CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT 序号, 工序, 工序周期*60 AS 周期 FROM @a ORDER BY 序号 DESC
OPEN cur
FETCH NEXT FROM cur INTO @seq,@process,@cycle
WHILE @@FETCH_STATUS=0
	BEGIN
		UPDATE @t SET	@flag = CASE WHEN @cycle>0 THEN 1 ELSE 0 END,
						@span = CASE WHEN @cycle<=[Minutes] THEN @cycle ELSE [Minutes] END,--如果工序需要的时间小于时段区间则整个工序完成，否则取时段值
						@cycle=@cycle-@span,		--还需要分配的时间
						[Minutes]=[Minutes]-@span,
						flag = @flag,
						EndTime = DATEADD(MINUTE,-@span,EndTime),
						processStartTime = DATEADD(MINUTE,-@span,EndTime),
						processEndTime= EndTime
		INSERT @processTime(process, StartTime, EndTime)
			SELECT @process, processStartTime, processEndTime FROM @t WHERE flag=1
		DELETE FROM @t WHERE [Minutes]=0
		FETCH NEXT FROM cur INTO @seq,@process,@cycle
	END
CLOSE cur
DEALLOCATE cur

SELECT *,DATEDIFF(MINUTE,StartTime,EndTime) Span FROM @processTime