Сравните дату и время двух разных таблиц и найдите не между столбцами
У меня есть таблица смен сотрудников, а затем таблица посещаемости. Я хотел бы знать смены, которые сотрудник не посещал.
Ниже приведен запрос на создание примеров таблиц.
DECLARE @InsideOutsideTable TABLE
(
FromTime DATETIME,
ToTime DATETIME
)
DECLARE @ShiftTable TABLE
(
FromDateTime DATETIME,
ToDateTime DATETIME
)
INSERT INTO @ShiftTable VALUES ('2018-05-02 07:30:00.000','2018-05-02 12:30:00.000')
INSERT INTO @ShiftTable VALUES ('2018-05-02 01:30:00.000','2018-05-02 16:30:00.000')
INSERT INTO @InsideOutsideTable VALUES ('2018-05-02 07:24:00.000','2018-05-02 11:47:00.000')
INSERT INTO @InsideOutsideTable VALUES ('2018-05-02 18:07:00.000','2018-05-02 18:32:00.000')
Ниже мой запрос, чтобы указать смены, на которых не присутствовал сотрудник.
SELECT
S.*
FROM @ShiftTable S
CROSS APPLY @InsideOutsideTable T
WHERE S.FromDateTime NOT BETWEEN T.FromTime AND T.ToTime
AND S.ToDateTime NOT BETWEEN T.FromTime AND T.ToTime
Но он возвращает каждую строку. Можете ли вы помочь мне выполнить мои требования?
Ожидаемый результат:
FromDateTime ToDateTime
2018-05-02 01:30:00.000 2018-05-02 16:30:00.000
Заранее большое спасибо.
У меня есть в основных таблицах. Поскольку этот вопрос предназначен только для логики, я привел здесь только отметки даты и времени.
Ответы 3
вы можете использовать это
DECLARE @InsideOutsideTable TABLE
(
FromTime DATETIME,
ToTime DATETIME
)
DECLARE @ShiftTable TABLE
(
ID Int,
FromDateTime DATETIME,
ToDateTime DATETIME
)
INSERT INTO @ShiftTable VALUES (1,'2018-05-02 07:30:00.000','2018-05-02 12:30:00.000')
INSERT INTO @ShiftTable VALUES (2,'2018-05-02 13:30:00.000','2018-05-02 16:30:00.000')
INSERT INTO @InsideOutsideTable VALUES ('2018-05-02 07:24:00.000','2018-05-02 11:47:00.000')
INSERT INTO @InsideOutsideTable VALUES ('2018-05-02 18:07:00.000','2018-05-02 18:32:00.000')
SELECT S.*
FROM @ShiftTable S
WHERE ID NOT IN (
SELECT
S.ID
FROM @ShiftTable S
INNER JOIN @InsideOutsideTable T
ON ( ( T.FromTime <= S.ToDateTime AND T.FromTime >= S.FromDateTime )
OR ( T.ToTime <= S.ToDateTime AND T.ToTime >= S.FromDateTime )
OR ( T.FromTime <= S.FromDateTime AND T.ToTime >= S.ToDateTime )
)
AND T.FromTime <= S.ToDateTime
)
позаботьтесь о том, чтобы вы определили 1:30, а не 13:30
Это также приносит все строки
эти две смены взаимосвязаны !!
Первая смена с 07:30 до 12:30, а в это время проводится регистрация заезда. Сотрудник пришел в 07:24 и вышел в 11:47. Он присутствовал на этой смене. Вторая смена с 13:30 до 16:30. В промежутке между сменами регистрация заезда не производится. Я хотел бы вернуть это
Я обновил свой ответ, позаботьтесь о том, чтобы вы определили 1:30, а не 13:30
Я вижу ваш обновленный ответ, но он не дает никаких результатов. :-(
Я просто запустил этот код на сервере sql, и у меня есть 1 строка
Используйте этот запрос:
SELECT *
FROM
(SELECT
S.*,
(SELECT
count(*)
FROM @InsideOutsideTable T
WHERE T.FromTime BETWEEN S.FromDateTime AND S.ToDateTime
OR T.ToTime BETWEEN S.FromDateTime AND S.ToDateTime) AS cnt
FROM @ShiftTable S) AS tbl
WHERE cnt = 0
Этот запрос приносит первую смену каждого сотрудника, который посещает. Первая смена с 07:30 до 12:30, а в это время проводится регистрация заезда. С 13:30 до 16:30 во вторую смену регистрация заезда не проводилась. Я хотел бы вернуть это.
Я думал, что присутствие означает, что вся продолжительность регистрации должна происходить в течение смены, но вы правы, каждое совпадение времени регистрации и смены означает присутствие, поэтому я изменил И на ИЛИ, где условие запроса, которое привлекает всех, должно Работа
Я думаю, вы ищете полное совпадение между двумя таблицами.
Если да, то самый простой подход - это LEFT JOIN или NOT EXISTS:
SELECT S.*
FROM @ShiftTable S LEFT JOIN
@InsideOutsideTable T
ON S.FromDateTime <= T.FromTime AND
S.ToDateTime >= T.ToTime
WHERE T.ToTime IS NULL;
Вот рекстестер.
Разве вы не пропустили ключ сотрудника в образце данных?