判断日期连续的问题

[qingyun67]

判断日期连续的问题
有一张表，有日期字段，现在想实现对于一个月的日期（2005-1-1----2005-1-31），进行判断，筛选出日期连续5天以上的记录包括5天，请问怎么做呢？

[lsxaa]

up

[Softlee81307]

UP

[xiajianfeng]

--try

CREATE PROCEDURE dbo.sp_Test
@Month AS DATETIME
AS
DECLARE @RowCount AS INT
DECLARE @StartID AS INT
DECLARE @EndID AS INT
DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @DiffCount AS INT
DECLARE @RecordFlag AS BIT

SET @StartID = 1
SET @EndID = 5
SET @DiffCount = 4
SET @RecordFlag = 0

SELECT IDENTITY(INT, 1, 1) AS ID_Num, InDate INTO #t FROM tb_1010 ORDER BY InDate

CREATE TABLE #t1 (ID_Num INT, InDate DATETIME)

--SELECT * FROM #t

SELECT @RowCount = MAX(ID_Num) FROM #t

IF @RowCount > 4
BEGIN
WHILE(1 = 1)
BEGIN
SELECT @StartDate = InDate FROM #t WHERE ID_Num = @StartID
WHILE(1 = 1)
BEGIN
SELECT @EndDate = InDate FROM #t WHERE ID_Num = @EndID
IF DATEDIFF(day, @StartDate, @EndDate) = @DiffCount
BEGIN
INSERT INTO #t1 SELECT ID_Num, InDate FROM #t WHERE ID_Num BETWEEN @StartID AND @EndID AND ID_Num NOT IN
(SELECT ID_Num FROM #t1)
SET @EndID = @EndID + 1
SET @DiffCount = @DiffCount + 1
SET @RecordFlag = 1
END
ELSE
BEGIN
IF @RecordFlag = 1
BEGIN
SET @StartID = @StartID + @DiffCount
SET @RecordFlag = 0
END
ELSE
SET @StartID = @StartID + 1
SET @DiffCount = 4
SET @EndID = @StartID + 4
BREAK
END
END
IF @RowCount - @StartID < 4
BREAK
END
END

SELECT * FROM #t1 WHERE YEAR(InDate) = YEAR(@Month) AND MONTH(InDate) = MONTH(@Month)
DROP TABLE #t1
DROP TABLE #t
GO

--调用
EXEC sp_Test '2005-1-1'

[xiajianfeng]

--其中tb_1010为表名称，InDate为需要判断的日期

[xiajianfeng]

--如果日期有重复，则修改如下：

CREATE PROCEDURE dbo.sp_Test
@Month AS DATETIME
AS
DECLARE @RowCount AS INT
DECLARE @StartID AS INT
DECLARE @EndID AS INT
DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @DiffCount AS INT
DECLARE @RecordFlag AS BIT

SET @StartID = 1
SET @EndID = 5
SET @DiffCount = 4
SET @RecordFlag = 0

SELECT IDENTITY(INT, 1, 1) AS ID_Num,InDate INTO #t FROM tb_1010 GROUP BY InDate ORDER BY InDate

CREATE TABLE #t1 (ID_Num INT, InDate DATETIME)

--SELECT * FROM #t

SELECT @RowCount = MAX(ID_Num) FROM #t

IF @RowCount > 4
BEGIN
WHILE(1 = 1)
BEGIN
SELECT @StartDate = InDate FROM #t WHERE ID_Num = @StartID
WHILE(1 = 1)
BEGIN
SELECT @EndDate = InDate FROM #t WHERE ID_Num = @EndID
IF DATEDIFF(day, @StartDate, @EndDate) = @DiffCount
BEGIN
INSERT INTO #t1 SELECT ID_Num, InDate FROM #t WHERE ID_Num BETWEEN @StartID AND @EndID AND ID_Num NOT IN
(SELECT ID_Num FROM #t1)
SET @EndID = @EndID + 1
SET @DiffCount = @DiffCount + 1
SET @RecordFlag = 1
END
ELSE
BEGIN
IF @RecordFlag = 1
BEGIN
SET @StartID = @StartID + @DiffCount
SET @RecordFlag = 0
END
ELSE
SET @StartID = @StartID + 1
SET @DiffCount = 4
SET @EndID = @StartID + 4
BREAK
END
END
IF @RowCount - @StartID < 4
BREAK
END
END

SELECT * FROM #t1 WHERE YEAR(InDate) = YEAR(@Month) AND MONTH(InDate) = MONTH(@Month)
DROP TABLE #t1
DROP TABLE #t
GO