对于优先级 > 8.5 的值,这似乎对我来说效果很好:
DECLARE @t TABLE(TicketID INT, DateTImeLogged DATETIME, Priority INT);
INSERT @t SELECT 1,'20110704 11:26:19.510',30
UNION ALL SELECT 2,'20110704 13:58:45.683',30
UNION ALL SELECT 3,'20110705 10:09:16.923',10
UNION ALL SELECT 4,'20110705 13:13:30.237',30
UNION ALL SELECT 5,'20110705 16:50:34.033',20;
SELECT TicketID
, DateTimeLogged --Type: Datetime
, Priority --Type: int
, [dbo].[fn_GetBusinessHour](DateTimeLogged, Priority)
FROM @t;
Yields:
TicketID DateTimeLogged Priority (No column name)
-------- ----------------------- -------- -----------------------
1 2011-07-04 11:26:19.510 30 2011-07-07 15:26:19.510
2 2011-07-04 13:58:45.683 30 2011-07-07 17:58:45.683
3 2011-07-05 10:09:16.923 10 2011-07-06 11:09:16.923
4 2011-07-05 13:13:30.237 30 2011-07-08 17:13:30.237
5 2011-07-05 16:50:34.033 20 2011-07-07 19:50:34.033
如果我添加另一行优先级
INSERT @t SELECT 6,'20110705 13:13:30.237',5;
然后将此行添加到结果中:
TicketID DateTimeLogged Priority (No column name)
-------- ----------------------- -------- -----------------------
6 2011-07-05 13:13:30.237 5 NULL
换句话说,如果函数逻辑未分配@CalculatedDate,则该函数将输出NULL,如果@addDayCount = 0,就会发生这种情况。在函数中,您说:
IF(@addDayCount != 0)
SET @CalcuatedDate = DATEADD(DD, @addDayCount, @date);
由于 @addDayCount 是 INT,请尝试以下操作:
DECLARE @addDayCount INT;
SET @addDayCount = 5 / 8.5;
SELECT @addDayCount;
Result:
0
因此,由于 @CalculatedDate 最初未分配值,因此以下所有 DATEADD 操作都执行 DATEADD(interval, number, NULL),但仍会产生 NULL。
因此,也许您需要对函数中的变量使用不同的数据类型......