В SQL Server часто необходимо анализировать и манипулировать данными на основе определенных условий или шаблонов. Одна из распространенных ситуаций – когда у нас есть таблица, содержащая сегменты выделения для сотрудников, и нам необходимо объединить непрерывные сегменты для одного и того же проекта. В этой статье мы рассмотрим, как достичь этого с помощью SQL-запросов или хранимых процедур.
Давайте рассмотрим таблицу с названием “emp_plan” со следующей структурой:
CREATE TABLE emp_plan (
empid NVARCHAR(200),
planid NVARCHAR(20),
startdate DATETIME,
enddate DATETIME
);
Чтобы проиллюстрировать концепцию, давайте вставим некоторые тестовые данные в таблицу:
INSERT INTO emp_plan(empid, planid, startdate, enddate) SELECT '001', 'planA', '2015-05-15', '2015-05-30' UNION ALL SELECT '001', 'planA', '2015-05-31', '2015-06-14' UNION ALL SELECT '001', 'planA', '2015-06-15', '2015-06-30' UNION ALL SELECT '001', 'planA', '2015-07-10', '2015-07-20' UNION ALL SELECT '001', 'planA', '2015-07-21', '2015-07-30' UNION ALL SELECT '001', 'planB', '2015-06-14', '2015-06-30' UNION ALL SELECT '001', 'planB', '2015-07-10', '2015-07-20' UNION ALL SELECT '001', 'planB', '2015-07-21', '2015-07-30' UNION ALL SELECT '002', 'planA', '2015-06-14', '2015-06-30' UNION ALL SELECT '002', 'planB', '2015-07-10', '2015-07-20' UNION ALL SELECT '002', 'planB', '2015-07-21', '2015-07-30';
Теперь давайте проанализируем данные в таблице. Мы видим, что сотруднику с empid 001 выделены непрерывные сегменты для одного и того же проекта (PlanA) в первых трех строках:
2015-05-15 до 2015-05-30 (1-я строка) 2015-05-31 до 2015-06-14 (2-я строка) 2015-06-15 до 2015-06-30 (3-я строка)
Однако четвертая строка содержит сегмент выделения, который не является непрерывным с предыдущими тремя сегментами, так как между конечной датой третьей строки и начальной датой четвертой строки есть разрыв. Затем четвертая строка следует за пятой строкой, которая продолжает тот же empid 001 и идентификатор проекта (PlanA):
2015-07-10 до 2015-07-20 (4-я строка) 2015-07-21 до 2015-07-30 (5-я строка)
Исходя из указанного правила, ожидаемый результат из примера данных должен быть:
Empid Planid Startdate Enddate 001 planA 2015-05-15 00:00:00.000 2015-06-30 00:00:00.000 (1-я до 3-й строки) 001 planA 2015-07-10 00:00:00.000 2015-07-30 00:00:00.000 (4-я до 5-й строки) 001 planB 2015-06-14 00:00:00.000 2015-06-30 00:00:00.000 001 planB 2015-07-10 00:00:00.000 2015-07-30 00:00:00.000 002 planA 2015-06-14 00:00:00.000 2015-06-30 00:00:00.000 002 planB 2015-07-10 00:00:00.000 2015-07-30 00:00:00.000
Для достижения этого результата мы можем использовать следующий запрос:
SELECT empid, planid, MIN(startdate) AS Startdate, MAX(enddate) AS Enddate
FROM (
SELECT empid, planid, startdate, enddate,
ROW_NUMBER() OVER (PARTITION BY empid, planid ORDER BY startdate) AS rn,
DATEDIFF(DAY, LAG(enddate) OVER (PARTITION BY empid, planid ORDER BY startdate), startdate) AS gap
FROM emp_plan
) AS t
GROUP BY empid, planid, DATEADD(DAY, -rn, startdate), DATEADD(DAY, -rn, enddate) - rn
ORDER BY empid, planid, Startdate;
Этот запрос использует функцию ROW_NUMBER() для присвоения номера строки каждому сегменту выделения для каждого сотрудника и проекта. Он также вычисляет разрыв между конечной датой предыдущего сегмента и начальной датой текущего сегмента. Группируя данные на основе empid, planid и вычисленных значений, мы можем объединить непрерывные сегменты выделения.
В заключение, понимание и манипулирование непрерывными сегментами выделения в SQL Server можно достичь с помощью функции ROW_NUMBER() и соответствующей группировки. Анализируя данные и определяя разрывы, мы можем объединить сегменты и получить желаемый набор результатов.