SQL для определения нескольких диапазонов дат (SQL Server 2000)
У меня есть таблица, которая содержит ID и Date для события. Каждая строка для одной даты. Я пытаюсь определить последовательные диапазоны дат и объединить выходные данные, чтобы показать ID,StartDate,EndDate
ID Date
200236 2011-01-02 00:00:00.000
200236 2011-01-03 00:00:00.000
200236 2011-01-05 00:00:00.000
200236 2011-01-06 00:00:00.000
200236 2011-01-07 00:00:00.000
200236 2011-01-08 00:00:00.000
200236 2011-01-09 00:00:00.000
200236 2011-01-10 00:00:00.000
200236 2011-01-11 00:00:00.000
200236 2011-01-12 00:00:00.000
200236 2011-01-13 00:00:00.000
200236 2011-01-15 00:00:00.000
200236 2011-01-16 00:00:00.000
200236 2011-01-17 00:00:00.000
Вывод будет выглядеть так:
ID StartDate EndDate
200236 2011-01-02 2011-01-03
200236 2011-01-05 2011-01-13
200236 2011-01-15 2011-01-17
Любые мысли о том, как справиться с этим в SQL Server 2000?
5 ответов
SELECT ...
FROM ...
WHERE date_column BETWEEN '2011-01-02' AND '2011-01-15'
возможно? Ссылка
Или вы можете выполнить подзапрос и связать следующую запись, используя MAX, где date <= current date:
SELECT id, date, (SELECT MAX(date) FROM mytable WHERE date <= mytable.date) AS nextDate
FROM mytable
Или используйте:
SELECT TOP 1 date
FROM mytable
WHERE date <= mytable.date AND id <> mytable.id
ORDER BY date
Как подзапрос, так он захватывает следующую дату в строке после текущей записи.
Я только что сделал это в SQL Server 2008. Я думаю, что следующий перевод будет работать для SQL Server 2000:
-- Create table variable
DECLARE @StartTable TABLE
(
rowid INT IDENTITY(1,1) NOT NULL,
userid int,
startDate date
)
Insert Into @StartTable(userid, startDate)
--This finds the start dates by finding unmatched values
SELECT t1.ID, t1.[Date]
FROM Example As t1
LEFT OUTER JOIN Example As t2 ON t1.ID=t2.ID
And DateAdd(day, 1, t2.[Date]) = t1.[Date]
WHERE t2.[Date] Is NULL
ORDER BY t1.ID, t1.[Date]
-- Create table variable
DECLARE @EndTable TABLE
(
rowid INT IDENTITY(1,1) NOT NULL,
userid int,
endDate date
)
Insert Into @EndTable(userid, endDate)
--This finds the end dates by getting unmatched values
SELECT t1.ID, t1.[Date]
FROM Example As t1
LEFT OUTER JOIN Example As t2 ON t1.ID=t2.ID
And DateAdd(day, -1, t2.[Date]) = t1.[Date]
WHERE t2.[Date] IS NULL
ORDER BY t1.ID, t1.[Date]
Select eT.userid, startDate, endDate
From @EndTable eT
INNER JOIN @StartTable sT On eT.userid = sT.userid
AND eT.rowid = sT.rowid;
Итак, как вы можете видеть, я создал две переменные таблицы, одну для начала и одну для конца, путем самостоятельного соединения таблицы с датой либо непосредственно перед, либо сразу после даты в столбце [Date]. Это означает, что для стартовой таблицы я выбираю только те записи, у которых нет предшествующей даты (так, чтобы они были в начале периода), а также те, у которых нет следующей даты (поэтому они будут в конце период) для таблицы конца.
Когда они вставляются в переменную таблицы, они последовательно нумеруются из-за столбца Identity. Затем я соединяю две переменные таблицы вместе. Поскольку они заказаны, даты начала и окончания всегда должны совпадать.
Это решение работает для меня, потому что у меня есть не более одной записи на каждый идентификатор в день, и меня интересуют только дни, а не часы и т. Д. Несмотря на то, что это несколько шагов, мне это нравится, потому что это концептуально просто и устраняет сопоставленные записи без необходимости курсоры или петли. Я надеюсь, что это сработает и для вас.
Один из подходов, который вы можете использовать, - это добавить поле, которое указывает следующую дату в последовательности. (Либо добавьте его в текущую таблицу, либо используйте временную таблицу, сохраните базовые данные во временной таблице, а затем обновите следующую дату в последовательности).
Ваша исходная структура данных будет выглядеть примерно так:
ID, PerfDate, NextDate
200236, 2011-01-02, 2011-01-03
200236, 2011-01-03, 2011-01-04
etc.
Затем вы можете использовать серию коррелированных подзапросов, чтобы свернуть данные в желаемый результат:
SELECT ID, StartDate, EndDate
FROM (
SELECT DISTINCT ID, PerfDate AS StartDate,
(SELECT MIN([PerfDate]) FROM [SourceTable] S3
WHERE S3.ID = S1.ID
AND S3.NextDate > S1.PerfDate
AND ISNULL(
(SELECT MIN(PerfDate)
FROM [SourceTable] AS S4
WHERE S4.ID = S1.ID
AND S4.NextDate > S3.NextDate), S3.NextDate + 1) > S3.NextDate) AS EndDate
FROM [SourceTable] S1
WHERE
ISNULL(
(SELECT MAX(NextDate)
FROM [SourceTable] S2
WHERE S2.ID = S1.ID
AND S2.PerfDate < S1.PerfDate), PerfDate -1) < S1.PerfDate)q
ORDER BY q.ID, q.StartDate
Это способ, которым я делал это в прошлом. Это двухэтапный процесс:
- Построить набор кандидатов непрерывных периодов
- Если есть какие-либо перекрывающиеся периоды, удалите все, кроме самого длинного такого периода.
Вот скрипт, который показывает, как это делается. Возможно, вы сможете выполнить это в одном [ошибочном, безобразном] запросе, но попытка сделать это заставит мою голову болеть. Я использую временные таблицы, так как это значительно облегчает отладку.
drop table #source
create table #source
(
id int not null ,
dtCol datetime not null ,
-----------------------------------------------------------------------
-- ASSUMPTION 1: Each date must be unique for a given ID value.
-----------------------------------------------------------------------
unique clustered ( id , dtCol ) ,
-----------------------------------------------------------------------
-- ASSUMPTION 2: The datetime column only represents a day.
-- The value of the time component is always 00:00:00.000
-----------------------------------------------------------------------
check ( dtCol = convert(datetime,convert(varchar,dtCol,112),112) ) ,
)
go
insert #source values(1,'jan 1, 2011')
insert #source values(1,'jan 4, 2011')
insert #source values(1,'jan 5, 2011')
insert #source values(2,'jan 1, 2011')
insert #source values(2,'jan 2, 2011')
insert #source values(2,'jan 3, 2011')
insert #source values(2,'jan 5, 2011')
insert #source values(3,'jan 1, 2011')
insert #source values(4,'jan 1, 2011')
insert #source values(4,'jan 2, 2011')
insert #source values(4,'jan 3, 2011')
insert #source values(4,'jan 4, 2011')
go
insert #source values( 200236 , '2011-01-02')
insert #source values( 200236 , '2011-01-03')
insert #source values( 200236 , '2011-01-05')
insert #source values( 200236 , '2011-01-06')
insert #source values( 200236 , '2011-01-07')
insert #source values( 200236 , '2011-01-08')
insert #source values( 200236 , '2011-01-09')
insert #source values( 200236 , '2011-01-10')
insert #source values( 200236 , '2011-01-11')
insert #source values( 200236 , '2011-01-12')
insert #source values( 200236 , '2011-01-13')
insert #source values( 200236 , '2011-01-15')
insert #source values( 200236 , '2011-01-16')
insert #source values( 200236 , '2011-01-17')
go
drop table #candidate_range
go
create table #candidate_range
(
rowId int not null identity(1,1) ,
id int not null ,
dtFrom datetime not null ,
dtThru datetime not null ,
length as 1+datediff(day,dtFrom,dtThru) ,
primary key nonclustered ( rowID ) ,
unique clustered (id,dtFrom,dtThru) ,
)
go
--
-- seed the candidate range table with the set of all possible contiguous ranges for each id
--
insert #candidate_range ( id , dtFrom , dtThru )
select id = tFrom.id ,
valFrom = tFrom.dtCol ,
valThru = tThru.dtCol
from #source tFrom
join #source tThru on tThru.id = tFrom.id
and tThru.dtCol >= tFrom.dtCol
where 1+datediff(day,tFrom.dtCol,tThru.dtCol) = ( select count(*)
from #source t
where t.id = tFrom.id
and t.dtCol between tFrom.dtCol and tThru.dtCol
)
order by 1,2,3
go
--
-- compare the table to itself. If we find overlapping periods,
-- we'll keep the longest such period and delete the shorter overlapping periods.
--
delete t2
from #candidate_range t1
join #candidate_range t2 on t2.id = t1.id
and t2.rowId != t1.rowID
and t2.length < t1.length
and t2.dtFrom <= t1.dtThru
and t2.dtThru >= t1.dtFrom
go
Вот и все, что нужно сделать.
Этот ТАК Вопрос может помочь вам. Я напрямую связался с ответом Роба Фарли, поскольку считаю, что это аналогичная проблема.