--total time span to calculate difference
DECLARE @StartDate DATETIME = '2015-11-10 8:00:00AM',
@EndDate DATETIME = '2015-11-11 7:00:00PM'
--get the day of week (-1 because sunday is counted as first weekday)
DECLARE @StartDayOfWeek INT = (SELECT DATEPART(WEEKDAY, @StartDate)) -1
DECLARE @EndDayOfWeek INT = (SELECT DATEPART(WEEKDAY, @EndDate)) -1
--set the time span to exclude
DECLARE @InitialDOWToExclude TINYINT = 2
DECLARE @InitialTODToExclude VARCHAR(100) = '9:00:00 AM'
DECLARE @EndDOWToExclude TINYINT = 3
DECLARE @EndTODToExclude VARCHAR(100) = '6:00:00 PM'
--this will be the final output in hours
DECLARE @ElapsedHours INT = (SELECT DATEDIFF(HOUR, @StartDate, @EndDate))
DECLARE @WeeksBetween INT = (SELECT DATEDIFF(WEEK, @StartDate, @EndDate))
DECLARE @Iterator INT = 0
WHILE (@Iterator <= @WeeksBetween)
BEGIN
DECLARE @InitialDaysBetween INT = @StartDayOfWeek - @InitialDOWToExclude
DECLARE @StartDateToExclude DATETIME = (SELECT DATEADD(DAY, @InitialDaysBetween, DATEADD(WEEK, @Iterator, @StartDate)))
SET @StartDateToExclude =CAST(DATEPART(YEAR, @StartDateToExclude) AS VARCHAR(100))
+ CAST(DATEPART(MONTH, @StartDateToExclude) AS VARCHAR(100))
+ CAST(DATEPART(DAY, @StartDateToExclude) AS VARCHAR(100))
+ ' '
+ CAST(@InitialTODToExclude AS VARCHAR(100))
DECLARE @EndDaysBetween INT = @EndDayOfWeek - @EndDOWToExclude
DECLARE @EndDateToExclude DATETIME = (SELECT DATEADD(DAY, @EndDaysBetween, DATEADD(WEEK, @Iterator, @EndDate)))
SET @EndDateToExclude =CAST(DATEPART(YEAR, @EndDateToExclude) AS VARCHAR(100))
+ CAST(DATEPART(MONTH, @EndDateToExclude) AS VARCHAR(100))
+ CAST(DATEPART(DAY, @EndDateToExclude) AS VARCHAR(100))
+ ' '
+ CAST(@EndTODToExclude AS VARCHAR(100))
SET @ElapsedHours = @ElapsedHours - DATEDIFF(HOUR, @StartDateToExclude, @EndDateToExclude)
SET @Iterator = @Iterator + 1
END
SELECT @ElapsedHours
DECLARE @Table1 TABLE ([Id] INT, [Start] DATETIME, [End] DATETIME)
INSERT INTO @Table1 VALUES
(1, '2015-11-08 00:00:00', '2015-11-10 21:45:38'),
(2, '2015-11-09 00:00:00', '2015-11-11 21:45:38')
;
-- hours to exclude
WITH excludeCTE AS
(
SELECT *
FROM (VALUES('Tuesday', 9, 0), ('Wednesday', 0, 0)) AS T([Day], [Hour], [Amount])
UNION ALL
SELECT [Day], [Hour] + 1, [Amount]
FROM excludeCTE
WHERE ([Day] = 'Tuesday' AND [Hour] < 23) OR ([Day] = 'Wednesday' AND [Hour] < 18)
),
-- all hours between start and end
dateCTE AS
(
SELECT [Id],
[Start],
[End],
DATENAME(weekday, [Start])[Day],
DATENAME(hour, [Start])[Hour]
FROM @Table1 t
UNION ALL
SELECT cte.[Id],
DATEADD(HOUR, 1, cte.[Start]),
cte.[End],
DATENAME(weekday, DATEADD(HOUR, 1, cte.[Start]))[Day],
DATENAME(hour, DATEADD(HOUR, 1, cte.[Start]))[Hour]
FROM @Table1 t
JOIN dateCTE cte ON t.Id = cte.Id
WHERE DATEADD(HOUR, 1, cte.[Start]) <= t.[End]
)
SELECT t.[Id],
t.[Start],
t.[End],
SUM(COALESCE(e.[Amount], 1)) [Hours]
FROM @Table1 t
INNER JOIN dateCTE d ON t.[Id] = d.[Id]
LEFT JOIN excludeCTE e ON d.[Day] = e.[Day] AND d.[Hour] = e.[Hour]
GROUP BY t.[Id],
t.[Start],
t.[End]
OPTION (MAXRECURSION 0) -- allow more than 100 hours
在此基础上增加一个限制条件，即在任意两个日期之间只能有一个排除范围。
CREATE TABLE worktable (
_Id INT
, _Start DATETIME
, _End DATETIME
);
INSERT INTO worktable VALUES
(1, '2015-11-09 00:00:00', '2015-11-09 00:45:00') -- Start and End before excluded range
, (2, '2015-11-09 00:00:00', '2015-11-11 21:45:00') -- Start before, End after
, (3, '2015-11-09 00:00:00', '2015-11-10 21:00:00') -- Start before, End between
, (4, '2015-11-10 10:00:00', '2015-11-11 10:00:00') -- Start between, End between
, (5, '2015-11-10 10:00:00', '2015-11-11 21:45:00') -- Start between, End after
With getDates As (
SELECT _Id
, a = _Start
, b = _End
, c = DATEADD(hh, 9
, DATEADD(DAY,DATEDIFF(DAY, 0, _Start) / 7 * 7
+ 7 * Cast(Sign(1 - DatePart(dw, _Start)) + 1 as bit), 1))
, d = DATEADD(hh, 18
, DATEADD(DAY,DATEDIFF(DAY, 0, _Start) / 7 * 7
+ 7 * Cast(Sign(1 - DatePart(dw, _Start)) + 1 as bit), 2))
FROM worktable
), getDiff As (
SELECT c_a = DATEDIFF(mi, a, c)
, c_b = DATEDIFF(mi, b, c)
, b_d = DATEDIFF(mi, d, b)
, a, b, c, d, _id
FROM getDates
)
Select _id
, (c_a + ABS(c_a)) / 2
- (c_b + ABS(c_b)) / 2
+ (b_d + ABS(b_d)) / 2
FROM getDiff;
c 是起始日期后第一个星期二的日期 (在SQL中查找下一个星期几的日期)，根据 DATEFIRST 的值可能需要调整最后一个值。
d 是同一周中 c 后第一个星期三的日期。
Cast(Sign(a - b) + 1 as bit) 如果 a 大于等于 b 则为 1，否则为 0。
(x + ABS(x)) / 2 如果 x 不是负数，则为 x，否则为 0。
考虑到排除范围的经过时间的公式为：
+ (Exclusion Start - Start) If (Start < Exclusion Start)
- (Exclusion Start - End) If (End < Exclusion Start)
+ (End - Exclusion End) If (Exclusion End < End)
-- excluded range (weekday numbers run from 1 to 7)
declare @x datetime = /*ignore*/ '1900012' + /*start day # and time*/ '3 09:00am';
declare @y datetime = /*ignore*/ '1900012' + /* end day # and time*/ '4 06:00pm';
-- normalize date to 1900-01-21, which was a Sunday
declare @s datetime =
dateadd(day, 19 + datepart(weekday, @start), cast(cast(@start as time) as datetime));
declare @e datetime =
dateadd(day, 19 + datepart(weekday, @end), cast(cast(@end as time) as datetime));
-- split range into two parts, one before @x and the other after @y
-- each part collapses to zero when @s and @e respectively fall between @x and @y
select (
datediff(second, -- diff in minutes would truncate so count seconds
case when @s < @x then @s else @x end, -- minimum of @s, @x
case when @e < @x then @e else @x end -- minimum of @e, @x
) +
datediff(second,
case when @s > @y then @s else @y end, -- maximum of @s, @y
case when @e > @y then @e else @y end -- maximum of @e, @y
)
) / 60; -- convert seconds to minutes, truncating with integer division
datediff(minute, ...)，您将截断部分分钟并永远无法将它们加在一起：这就是为什么我计算秒数，然后在最后除以六十的原因。当然，如果您只处理整分钟，则不需要那样做。datefirst设置为星期天。如果需要，可以进行调整或更加可移植。smalldatetime，因为我知道1900年1月1日是星期一，而且我正在手机上输入我的答案。在旧版本中，dateadd总是可以正常工作的，但我发现它在2014年（在SQL Fiddle中）不再起作用了。我指的是weekday / dw。有些人非常讨厌较短的代码，所以我在发布之前进行了更改。我好像有记忆长代码的问题。 - shawnt00