Группировка по смежным датам, игнорирование выходных в SQL

Я пытаюсь сгруппировать смежные диапазоны дат, чтобы показать минимальную и максимальную дату для каждого диапазона. До сих пор я использовал решение, подобное этому: http://www.sqlservercentral.com/articles/T-SQL/71550/ однако я использую SQL 2000, поэтому мне пришлось внести некоторые изменения. Это моя процедура до сих пор:

create table #tmp 
(
date smalldatetime,
rownum int identity
)

insert into #tmp
select distinct date from testDates order by date

select 
min(date) as dateRangeStart,
max(date) as dateRangeEnd, 
count(*) as dates, 
dateadd(dd,-1*rownum, date) as GroupID 
from #tmp
group by dateadd(dd,-1*rownum, date)

drop table #tmp

Он работает именно так, как я хочу, за исключением одной проблемы: выходные. В моих наборах данных нет записей о выходных днях, что означает, что любая найденная группа составляет не более 5 дней. Например, в приведенных ниже результатах я хотел бы, чтобы последние 3 группы отображались как одна запись с dateRangeStart 10/6 и dateRangeEnd 10/20:

скриншот результатов

Есть ли способ настроить это, чтобы игнорировать перерыв в диапазоне дат, если этот перерыв - просто выходные?

Спасибо за помощь.


sql sql-date-functions sql-server-2000
person Colin    schedule 10.03.2011    source источник
comment
Почему бы не создать постоянную таблицу календаря со столбцом номера недели?   -  person Martin Smith    schedule 10.03.2011
comment
@ Мартин, можешь рассказать об этом подробнее? Я всегда могу получить номер недели, используя datepart, но я не понимаю, как это поможет изменить мою группировку.   -  person Colin    schedule 10.03.2011
comment
О, я неправильно прочитал задачу.   -  person Martin Smith    schedule 10.03.2011

Ответы (1)


arrow_upward
2
arrow_downward

ОТРЕДАКТИРОВАНО

Мне не очень нравилась моя предыдущая идея. Вот, по-моему, лучше:

  1. На основе первой и последней дат из набора тех, которые необходимо сгруппировать, подготовьте список всех промежуточных дат выходного дня.
  2. Вставьте рабочие даты вместе с датами выходных в порядке, чтобы всем им было присвоено rownum значений в соответствии с их обычным порядком.

  3. Используйте свой метод поиска смежных диапазонов со следующими изменениями:

    1) при расчете dateRangeStart, если это выходной день, выбрать ближайший следующий рабочий день;

    2) соответственно для dateRangeEnd, если это выходной день, выбрать ближайший предшествующий будний день;

    3) при подсчете дат для группы выбирать только рабочие дни.

  4. Выбрать из полученного множества только те строки, где dates > 0, исключив тем самым группы, образованные только из выходных.

А вот реализация метода, где предполагается, что неделя начинается в воскресенье (DATEPART возвращает 1), а выходные дни — воскресенье и суббота:

DECLARE @tmp TABLE (date smalldatetime, rownum int IDENTITY);
DECLARE @weekends TABLE (date smalldatetime);
DECLARE @minDate smalldatetime, @maxDate smalldatetime, @date smalldatetime;
/* #1 */
SELECT @minDate = MIN(date), @maxDate = MAX(date)
FROM testDates;
SET @date = @minDate - DATEPART(dw, @minDate) + 7;
WHILE @date < @maxDate BEGIN
  INSERT INTO @weekends
  SELECT @date UNION ALL
  SELECT @date + 1;
  SET @date = @date + 7;
END;
/* #2 */
INSERT INTO @tmp
SELECT date FROM testDates
UNION
SELECT date FROM @weekends
ORDER BY date;
/* #3 & #4 */
SELECT *
FROM (
  SELECT
    MIN(date + CASE DATEPART(dw, date) WHEN 1 THEN 1 WHEN 7 THEN 2 ELSE 0 END)
      AS dateRangeStart,
    MAX(date - CASE DATEPART(dw, date) WHEN 1 THEN 2 WHEN 7 THEN 1 ELSE 0 END)
      AS dateRangeEnd,
    COUNT(CASE WHEN DATEPART(dw, date) NOT IN (1, 7) THEN date END) AS dates,
    DATEADD(d, -rownum, date) AS GroupID
  FROM @tmp
  GROUP BY DATEADD(d, -rownum, date)
) s
WHERE dates > 0;
person Andriy M    schedule 11.03.2011
comment
Спасибо! Это прекрасно работает. Я думал о том, чтобы вставить даты выходного дня, чтобы решить проблему с диапазоном, но я подумал, что это будет сложнее, чем это. - person Colin; 11.03.2011