Блог

Действие может запускаться и останавливаться 0 или более раз в течение заданного периода времени. Каждый раз, когда это происходит, создается запись с датой и действием — я буду называть действия «Активировать (A)» и «Деактивировать (D)». Мне нужно подвести итог общему количеству «активных» дней в данном месяце. Я хотел бы простой способ сделать это, не загроможденный исключениями и особыми случаями. Мне придется сделать это в tsql.

Вот процедурный подход, который я использовал до сих пор:

1. Было ли событие start или stop в течение периода? а) если НЕТ, то найдите последнее событие, которое предшествует периоду времени i) если это событие было Start, то все дни периода активны ii) если событие было Stop, в периоде нет активных дней iii) выполнено б) если ДА … тогда крысиное гнездо процедурных проверок и if-thens.

Как обычно, я хотел бы понять, как другие увидят проблему, а не просто начать кодировать процедуру для сортировки записей в хронологическом порядке и перебирать их.

Типичный набор данных:

 6/11/2014  A
6/20/2014  D
6/24/2014  A
6/26/2014  D
6/29/2014  A
  

Здесь интересующий период — июнь, и первым действием было активировать — так что действие было неактивным до 6/11. Аналогично, 6/29 все снова становится активным, и поэтому оставшаяся часть месяца считается активной. Используя функцию datediff() для D и A дат, я получаю 9 2, а последний день месяца составляет 12 активных дней.

Опять же, только для набора данных за один период может быть 0 или более записей.

Естественно, данные могли бы также выглядеть следующим образом:

 6/11/2014  D
6/20/2014  A
6/24/2014  D
6/26/2014  A
6/29/2014  D
  

Здесь действие было «активным» до 6/11, когда оно было деактивировано. Просто datediff()’ing (D — A) dates дает мне дополнительные 4 3 дня, в общей сложности 17.

Я боюсь, что если я ВЫБЕРУ, ОТСОРТИРУЮ, а затем СДЕЛАЮ, пока я собираюсь обернуться вокруг оси. Интересно, какой подход slicker могли бы использовать другие.

Этот запрос предоставит вам все дни за данный период с соответствующим событием за день:

SQL Fiddle

Настройка схемы MS SQL Server 2008:

 CREATE TABLE Table1
    ([dt] date, [event] varchar(1))
;

INSERT INTO Table1
    ([dt], [event])
VALUES
    ('2014-06-11', 'A'),
    ('2014-06-20', 'D'),
    ('2014-06-24', 'A'),
    ('2014-06-26', 'D'),
    ('2014-06-29', 'A')
;
  

Запрос 1:

 declare @start date, @end date
select @start = '2014-06-01', @end = '2014-06-30'
;with dates as (
  SELECT @start AS d, (SELECT TOP 1 CASE WHEN event = 'A' THEN 'D' ELSE 'A' END FROM Table1 ORDER BY dt) AS e
  UNION ALL 
  SELECT dateadd(day, 1, d), COALESCE((SELECT event FROM Table1 WHERE dt = dateadd(day, 1, dates.d)), dates.e)
  FROM dates 
  WHERE dates.d < @end)
select * from dates
OPTION (MAXRECURSION 0)
  

Результаты:

 |          D | E |
|------------|---|
| 2014-06-01 | D |
| 2014-06-02 | D |
| 2014-06-03 | D |
| 2014-06-04 | D |
| 2014-06-05 | D |
| 2014-06-06 | D |
| 2014-06-07 | D |
| 2014-06-08 | D |
| 2014-06-09 | D |
| 2014-06-10 | D |
| 2014-06-11 | A |
| 2014-06-12 | A |
| 2014-06-13 | A |
| 2014-06-14 | A |
| 2014-06-15 | A |
| 2014-06-16 | A |
| 2014-06-17 | A |
| 2014-06-18 | A |
| 2014-06-19 | A |
| 2014-06-20 | D |
| 2014-06-21 | D |
| 2014-06-22 | D |
| 2014-06-23 | D |
| 2014-06-24 | A |
| 2014-06-25 | A |
| 2014-06-26 | D |
| 2014-06-27 | D |
| 2014-06-28 | D |
| 2014-06-29 | A |
| 2014-06-30 | A |
  

Используя приведенный выше запрос, вы сможете легко создавать запросы для подсчета, либо следующим образом:

 declare @start date, @end date
select @start = '2014-06-01', @end = '2014-06-30'
;with dates as (
  SELECT @start AS d, (SELECT TOP 1 CASE WHEN event = 'A' THEN 'D' ELSE 'A' END FROM Table1 ORDER BY dt) AS e
  UNION ALL 
  SELECT dateadd(day, 1, d), COALESCE((SELECT event FROM Table1 WHERE dt = dateadd(day, 1, dates.d)), dates.e)
  FROM dates 
  WHERE dates.d < @end)
select e, count(*) from dates GROUP BY e
OPTION (MAXRECURSION 0)
  

Результаты:

 | E | COLUMN_1 |
|---|----------|
| A |       13 |
| D |       17 |
  

или как это:

 declare @start date, @end date
select @start = '2014-06-01', @end = '2014-06-30'
;with dates as (
  SELECT @start AS d, (SELECT TOP 1 CASE WHEN event = 'A' THEN 'D' ELSE 'A' END FROM Table1 ORDER BY dt) AS e
  UNION ALL 
  SELECT dateadd(day, 1, d), COALESCE((SELECT event FROM Table1 WHERE dt = dateadd(day, 1, dates.d)), dates.e)
  FROM dates 
  WHERE dates.d < @end)
select 
sum(case when e='A' THEN 1 ELSE 0 END) as A_days,
sum(case when e='D' THEN 1 ELSE 0 END) as D_days
from dates
OPTION (MAXRECURSION 0)
  

Результаты:

 | A_DAYS | D_DAYS |
|--------|--------|
|     13 |     17 |