sql server,一个表里存着休假日历,有开始时间,结束时间,现在有一张工单,知道开工时间,和生产耗时,如何计算工单结束时间?
实质在计算每两个休假之间的有效工作时间,直接能满足工单的耗时。
建表语句
CREATE TABLE [dbo].[工厂日历](
[ID] [int] IDENTITY(1,1) NOT NULL,
[开始时间] [datetime] NULL,
[结束时间] [datetime] NULL,
CONSTRAINT [PK_日历_ID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
函数
ALTER FUNCTION [dbo].[CalculateWorkOrderEndTime]
(
@WorkOrderStartTime DATETIME,
@ProductionDurationSeconds INT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @WorkOrderEndTime DATETIME;
DECLARE @CurrentTime DATETIME = @WorkOrderStartTime;
DECLARE @ElapsedTime INT = 0;
-- 循环直到生产耗时用完
WHILE @ElapsedTime < @ProductionDurationSeconds
BEGIN
-- 检查当前时间是否在休假期间
IF EXISTS (
SELECT 1
FROM [dbo].[工厂日历]
WHERE @CurrentTime >= [开始时间] AND @CurrentTime < [结束时间]
)
BEGIN
-- 如果在休假期间,跳到休假结束时间
SELECT TOP 1 @CurrentTime = [结束时间]
FROM [dbo].[工厂日历]
WHERE @CurrentTime >= [开始时间] AND @CurrentTime < [结束时间]
ORDER BY [结束时间] ASC;
-- 为了避免边界问题,将当前时间设置为休假结束后的第一秒
SET @CurrentTime = DATEADD(SECOND, 1, @CurrentTime);
END
ELSE
BEGIN
-- 如果不在休假期间,则消耗生产时间
SET @ElapsedTime = @ElapsedTime + DATEDIFF(SECOND, @CurrentTime, DATEADD(SECOND, 1, @CurrentTime));
SET @CurrentTime = DATEADD(SECOND, 1, @CurrentTime);
END
END
-- 设置工单结束时间为当前时间(已经考虑完所有休假和生产耗时)
SET @WorkOrderEndTime = @CurrentTime;
RETURN @WorkOrderEndTime;
END
测试