使用Chris提到的时间段(不要与时间强盗混淆):
CREATE TABLE Start_Periods
(
begin_time TIME NOT NULL,
end_time TIME NOT NULL,
time_period TINYINT NOT NULL
CONSTRAINT PK_Start_Periods PRIMARY KEY CLUSTERED (begin_time),
CONSTRAINT CK_Start_Periods_begin_before_end CHECK (begin_time < end_time OR end_time = '00:00:00.000')
)
INSERT INTO Start_Periods (begin_time, end_time, time_period)
SELECT '00:00:00.000', '00:15:00.000', 1 UNION ALL
SELECT '00:15:00.000', '00:45:00.000', 2 UNION ALL
SELECT '00:45:00.000', '01:15:00.000', 3 UNION ALL
SELECT '01:15:00.000', '01:45:00.000', 4 UNION ALL
SELECT '01:45:00.000', '02:15:00.000', 5 UNION ALL
SELECT '02:15:00.000', '02:45:00.000', 6 UNION ALL
SELECT '02:45:00.000', '03:15:00.000', 7 UNION ALL
SELECT '03:15:00.000', '03:45:00.000', 8 UNION ALL
SELECT '23:15:00.000', '23:45:00.000', 48 UNION ALL
SELECT '23:45:00.000', '00:00:00.000', 1
你的查询变成了:
SELECT
SCH.employee_code,
COUNT(DISTINCT SP.time_period) AS different_time_starts
FROM
Schedule SCH
INNER JOIN Start_Periods SP ON
SP.begin_time <= SCH.start_time AND
SP.end_time > SCH.start_time
GROUP BY
SCH.employee_code