SQL Server 2008 R2:如何获取可用空格 时间之间

Question

我在表格中有两列，分别表示员工工作时间的start和end(它存储HH：MM：SS(。我有另一个表作为堆栈工作，用于存储忙碌时间。假设我将其存储为员工的结果，其中start为 8：00：00，end为 17：00：00

startColumn         endColumn
08:15:00.0000000    08:45:00.0000000
11:00:00.0000000    12:00:00.0000000
12:00:00.0000000    13:00:00.0000000

考虑到上述数据在表中，我想得到以下结果：

8:00 A.M
8:45 A.M
10:00 A.M
1:00 P.M
2:00 P.M
3:00 P.M
4:00 P.M

提前感谢！

Answer 1

编辑：这是一个四分之一小时的解决方案，在没有工作活动的情况下返回所有季度......

DECLARE @empl TABLE(ID INT, EmplName VARCHAR(100));
INSERT INTO @empl VALUES
(1,'Mr. X');
DECLARE @emplStandard TABLE(emplID INT, StartHour TIME, EndHour TIME);
INSERT INTO @emplStandard VALUES
(1,{t'08:00:00'},{t'17:00:00'});
DECLARE @Work TABLE(emplID INT,WorkDay DATE,StartHour TIME,EndHour TIME);
INSERT INTO @Work VALUES
 (1,{d'2016-02-05'},{t'08:15:00'},{t'08:45:00'})
,(1,{d'2016-02-05'},{t'11:00:00'},{t'12:00:00'})
,(1,{d'2016-02-05'},{t'12:00:00'},{t'13:00:00'});
WITH TallyTimes(TheHour) AS
(
          SELECT {t'08:00:00'}
    UNION SELECT {t'08:15:00'}
    UNION SELECT {t'08:30:00'}
    UNION SELECT {t'08:45:00'}
    UNION SELECT {t'09:00:00'}
    UNION SELECT {t'09:15:00'}
    UNION SELECT {t'09:30:00'}
    UNION SELECT {t'09:45:00'}
    UNION SELECT {t'10:00:00'}
    UNION SELECT {t'10:15:00'}
    UNION SELECT {t'10:30:00'}
    UNION SELECT {t'10:45:00'}
    UNION SELECT {t'11:00:00'}
    UNION SELECT {t'11:15:00'}
    UNION SELECT {t'11:30:00'}
    UNION SELECT {t'11:45:00'}
    UNION SELECT {t'12:00:00'}
    UNION SELECT {t'12:15:00'}
    UNION SELECT {t'12:30:00'}
    UNION SELECT {t'12:45:00'}
    UNION SELECT {t'13:00:00'}
    UNION SELECT {t'13:15:00'}
    UNION SELECT {t'13:30:00'}
    UNION SELECT {t'13:45:00'}
    UNION SELECT {t'14:00:00'}
    UNION SELECT {t'14:15:00'}
    UNION SELECT {t'15:30:00'}
    UNION SELECT {t'16:45:00'}
    UNION SELECT {t'15:00:00'}
    UNION SELECT {t'15:15:00'}
    UNION SELECT {t'15:30:00'}
    UNION SELECT {t'15:45:00'}
    UNION SELECT {t'16:00:00'}
    UNION SELECT {t'16:15:00'}
    UNION SELECT {t'16:30:00'}
    UNION SELECT {t'16:45:00'}
    UNION SELECT {t'17:00:00'}
    UNION SELECT {t'17:15:00'}
    UNION SELECT {t'17:30:00'}
    UNION SELECT {t'17:45:00'}
)
SELECT e.EmplName
      ,CAST(CAST(tt.TheHour AS TIME) AS VARCHAR(8)) AS TheHour
FROM TallyTimes AS tt
CROSS JOIN @empl AS e
INNER JOIN @emplStandard AS es ON es.emplID=e.ID
WHERE NOT EXISTS(SELECT 1 
                 FROM @Work AS w 
                 WHERE w.emplID=e.ID 
                   AND CAST(tt.TheHour AS TIME)>=w.StartHour AND  CAST(tt.TheHour AS TIME)<=w.EndHour
                 )

在第一次尝试之后(在问题更改之前(

如果我理解正确，您只处理整小时：

这将挑选出您的员工工作时间：

简短说明：在声明的表变量中填充测试数据后，我从 CTE 开始，创建从 06：00 到 20：00 的所有小时的列表。最后SELECT检查表"工作"中是否存在当前小时是否在间隔内的条目。是否包含EndHour由您决定。从这个角度来看，您自己的示例数据不一致。

如果您真的只想要非工作时间，您可以将CASE WHEN逻辑转换为WHERE......

请注意，您将需要进一步的逻辑来区分不同的员工，工作日以及每个工作日(周一以外的星期五(的不同时间标准。

DECLARE @empl TABLE(ID INT, EmplName VARCHAR(100));
INSERT INTO @empl VALUES
(1,'Mr. X');
DECLARE @emplStandard TABLE(emplID INT, StartHour TIME, EndHour TIME);
INSERT INTO @emplStandard VALUES
(1,{t'08:00:00'},{t'17:00:00'});
DECLARE @Work TABLE(emplID INT,WorkDay DATE,StartHour TIME,EndHour TIME);
INSERT INTO @Work VALUES
 (1,{d'2016-02-05'},{t'08:00:00'},{t'09:00:00'})
,(1,{d'2016-02-05'},{t'11:00:00'},{t'12:00:00'})
,(1,{d'2016-02-05'},{t'12:00:00'},{t'13:00:00'});

WITH TallyTimes(TheHour) AS
(
          SELECT {t'06:00:00'}
    UNION SELECT {t'07:00:00'}
    UNION SELECT {t'08:00:00'}
    UNION SELECT {t'09:00:00'}
    UNION SELECT {t'10:00:00'}
    UNION SELECT {t'11:00:00'}
    UNION SELECT {t'12:00:00'}
    UNION SELECT {t'13:00:00'}
    UNION SELECT {t'14:00:00'}
    UNION SELECT {t'15:00:00'}
    UNION SELECT {t'16:00:00'}
    UNION SELECT {t'17:00:00'}
    UNION SELECT {t'18:00:00'}
    UNION SELECT {t'19:00:00'}
    UNION SELECT {t'20:00:00'}
)
SELECT e.EmplName
      ,tt.TheHour
      ,CASE WHEN EXISTS(SELECT 1 
                        FROM @Work AS w 
                        WHERE w.emplID=e.ID 
                          AND CAST(tt.TheHour AS TIME)>=w.StartHour AND  CAST(tt.TheHour AS TIME)<=w.EndHour
                        ) THEN 'X' ELSE '' END
FROM TallyTimes AS tt
CROSS JOIN @empl AS e
INNER JOIN @emplStandard AS es ON es.emplID=e.ID

结果：

Empl    TheHour   WasWorking
Mr. X   06:00:00    
Mr. X   07:00:00    
Mr. X   08:00:00    X
Mr. X   09:00:00    X
Mr. X   10:00:00    
Mr. X   11:00:00    X
Mr. X   12:00:00    X
Mr. X   13:00:00    X
Mr. X   14:00:00    
Mr. X   15:00:00    
Mr. X   16:00:00    
Mr. X   17:00:00    
Mr. X   18:00:00    
Mr. X   19:00:00    
Mr. X   20:00:00    

Answer 2

对于其他任何寻找同样的东西的人来说，这就是我所做的

DECLARE @Contador INT
DECLARE @start TIME
DECLARE @end TIME
DECLARE @request INT
DECLARE @Max INT
DECLARE @Libres TABLE(Id INT,startTime TIME, endTime TIME)
DECLARE @Result TABLE(Id INT,Horario VARCHAR(8))
SET @Contador = 1
INSERT INTO @Libres VALUES (1,'08:00:00.0000000','10:00:00.0000000');
INSERT INTO @Libres VALUES (2,'11:00:00.0000000','14:00:00.0000000');
INSERT INTO @Libres VALUES (3,'14:00:00.0000000','15:00:00.0000000');
SET @Max = (SELECT MAX(id) Id FROM @Libres)
WHILE(@Contador <= (SELECT COUNT(*) FROM @Libres))
        BEGIN
            SET @START = (SELECT startTime FROM @Libres Where Id = @Contador)
            SET @END = (SELECT DATEADD(MINUTE, -60 ,endTime) FROM @Libres Where Id = @Contador)
            SET @REQUEST = 1
            ;WITH Dates AS (
            SELECT @request AS Id,@Start AS reqDate
            UNION all
            SELECT Id+1,DATEADD(MINUTE,60,reqDate) FROM Dates
            WHERE reqDate < @end
            )
            INSERT INTO @Result(Id,Horario)
            SELECT Id,CONVERT(VARCHAR(8),reqDate,100) "Horarios" FROM Dates
            SET @Contador = @Contador + 1
            IF(@Max < @Contador)
                BREAK;
            ELSE
                CONTINUE;
        END
SELECT * FROM @Result