SQL确定多个日期范围(SQL Server 2000) [英] SQL to determine multiple date ranges (SQL Server 2000)

问题描述

我有一个表，其中包含一个事件的ID和Date.每行代表一个日期.我正在尝试确定连续的日期范围并合并输出以显示ID,StartDate,EndDate

I have a table which contains an ID and a Date for an event. Each row is for one date. I am trying to determine consecutive date ranges and consolidate output to show the 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中处理此问题的想法?

Any thoughts on how to handle this in SQL Server 2000?

推荐答案

我刚刚在SQL Server 2008中完成了类似的操作.我认为以下翻译将适用于SQL Server 2000:

I've just done this similar thing in SQL Server 2008. I think the following translation will work for 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;

如您所见，我通过在[日期]列中的日期之前或之后的日期自联接表，创建了两个表变量，一个用于开始，一个用于结束.这意味着我只为开始表选择没有日期之前的记录(因此这些记录将在一个周期的开始)和没有日期之后的记录(因此这些记录将在a的末尾)期表).

So as you can see, I created two table variables, one for starts and one for ends, by self-joining the table on the date either just prior to or just after the date in the [Date] column. This means that I'm selecting only records that don't have a date prior (so these would be at the beginning of a period) for the Start Table and those that have no date following (so these would be at the end of a period) for the End Table.

将它们插入表变量时，由于标识"列，它们按顺序编号.然后，我将两个表变量连接在一起.因为它们是有序的，所以开始日期和结束日期应该始终正确匹配.

When these are inserted into the table variable, they are numbered in sequence because of the Identity column. Then I join the two table variables together. Because they are ordered, the start and end dates should always match up properly.

此解决方案对我有用，因为每天每个ID最多有一条记录，并且我只对几天而不是小时等感兴趣.即使分几个步骤，我也喜欢它，因为它从概念上讲很简单，并且消除了匹配项.记录而没有游标或循环.我希望它也对您有用.

This solution works for me because I have at most one record per ID per day and I am only interested in days, not hours, etc. Even though it is several steps, I like it because it is conceptually simple and eliminates matched records without having cursors or loops. I hope it will work for you too.

这篇关于SQL确定多个日期范围(SQL Server 2000)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！