Запрос дат SQL - как долго это условие было истинным

Мое решение от SQL Server — такое же, как у Dems, но я сам установил минимальную базовую планку. Предполагается, что пропусков нет, то есть есть запись на каждый день для каждого человека. Если это не так, мне придется зацикливаться.

DECLARE @run_date datetime
DECLARE @min_date datetime

SET @run_date = {d '2009-01-03'}

-- get day before any entries in the table to use as a false baseline date
SELECT @min_date = DATEADD(day, -1, MIN(processing_date)) FROM history_of_jerkiness

-- get last not a jerk date for each name that is before or on the run date
-- the difference in days between the run date and the last not a jerk date is the number of days as a jerk
SELECT [name], DATEDIFF(day, MAX(processing_date), @run_date)
FROM (
     SELECT processing_date, [name], is_jerk
     FROM history_of_jerkiness
     UNION ALL
     SELECT DISTINCT @min_date, [name], 0
     FROM history_of_jerkiness ) as data
WHERE is_jerk = 0
  AND processing_date <= @run_date
GROUP BY [name]
HAVING DATEDIFF(day, MAX(processing_date), @run_date) > 0

Я создал тестовую таблицу со следующим:

CREATE TABLE history_of_jerkiness (processing_date datetime, [name] varchar(20), is_jerk bit)

INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-01'}, 'Matt', 1)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-01'}, 'Bob', 0)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-01'}, 'Alex', 1)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-01'}, 'Carol', 1)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-02'}, 'Matt', 1)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-02'}, 'Bob', 1)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-02'}, 'Alex', 0)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-02'}, 'Carol', 1)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-03'}, 'Matt', 1)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-03'}, 'Bob', 1)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-03'}, 'Alex', 1)
INSERT INTO history_of_jerkiness (processing_date, [name], is_jerk) VALUES ({d '2009-01-03'}, 'Carol', 0) 

Это можно упростить, если вы структурируете данные так, чтобы они соответствовали следующим критериям...

У всех людей должна быть исходная запись, где они не придурки

Вы можете сделать что-то вроде...

SELECT
   name,
   MAX(date)   last_day_jerk_free
FROM
   jerkiness AS [data]
WHERE
   jerk = 'false'
   AND date <= 'a date'
GROUP BY
   name

Вы уже знаете, что такое базовая дата («свидание»), и теперь вы знаете, что в последний день они не были придурком. Я не знаю sybase, но я уверен, что есть команды, которые вы можете использовать, чтобы получить количество дней между «a data» и «last_day_jerk_free».

РЕДАКТИРОВАТЬ:

Существует несколько способов искусственного создания инициализирующей «не прерывистой» записи. Тот, который предложил Уилл Рикардс, использует подзапрос, содержащий объединение. Это, однако, имеет две отрицательные стороны...
1. Подзапрос маскирует любые индексы, которые могли бы быть использованы в противном случае
2. Он предполагает, что все люди имеют данные, начиная с одной и той же точки

В качестве альтернативы, примите предложение Уилла Рикарда и переместите агрегацию из внешнего запроса во внутренний запрос (чтобы максимизировать использование индексов) и объедините с обобщенным вторым подзапросом, чтобы создать начальную отрывочную = ложную запись...

SELECT name, DATEDIFF(day, MAX(processing_date), @run_date) AS days_jerky
FROM (

    SELECT name, MAX(processing_date) as processing_date
    FROM history_of_jerkiness
    WHERE is_jerk = 0 AND processing_date <= @run_date
    GROUP BY name

    UNION

    SELECT name, DATEADD(DAY, -1, MIN(processing_date))
    FROM history_of_jerkiness
    WHERE processing_date <= @run_date
    GROUP BY name

    ) as data
GROUP BY
   name

Внешний запрос по-прежнему должен выполнять максимум без индексов, но с уменьшенным количеством записей (2 на имя, а не n на имя). Количество записей также сокращается за счет того, что не требуется, чтобы каждое имя имело значение для каждой используемой даты. Есть много других способов сделать это, некоторые из них можно увидеть в моей истории редактирования.

«Это можно упростить, если вы структурируете данные так, чтобы они соответствовали следующим критериям…

У всех людей должна быть первоначальная запись, где они не придурки"

Каким критериям должны и не должны соответствовать данные, зависит от пользователя, а не от разработчика.

Как насчет этого:

select a.name,count(*) from history_of_jerkiness a
left join history_of_jerkiness b
on a.name = b.name 
and a.processing_date >= b.processing_date
and a.is_jerk = 'true'
where not exists
( select * from history_of_jerkiness c
  where a.name = c.name
  and c.processing_date between a.processing_date and b.processing_date
  and c.is_jerk = 'false'
)
and a.processing_date <= :a_certain_date;