获得连续记录 [英] Getting Consecutive Records

问题描述

我需要根据间隔限制对记录进行分组

I need to group the records based on the interval limit

ID	Name	Start Date	End Date	Interval
1	Name1	01/02/2013	05/02/2013	0
2	Name1	15/02/2013	20/02/2013	10
3	Name1	01/03/2013	05/03/2013	9
4	Name1	01/04/2013	05/04/2013	27
5	Name1	18/04/2013	20/04/2013	13
6	Name1	28/04/2013	30/04/2013	8
7	Name1	01/05/2013	05/05/2013	1
8	Name1	15/05/2013	20/05/2013	10
9	Name1	25/05/2013	30/05/2013	5

允许间隔： 10天



以上记录

行 1,2,3 是连续的，因为EndDate - Previous记录的StartDate少于允许的间隔。

行 4 是独立的，因为间隔超过允许的间隔。



行 5,6,7,8,9 是连续的。

Allowed Interval : 10days

in the above records
Rows 1,2,3 are consecutive as the EndDate - StartDate of the Previous record is less the allowed interval.
Row 4 is separate as the Interval is exceeding the allowed interval.

Rows 5,6,7,8,9 are consecutive.

推荐答案

我不确定你想要实现的目标。 ..



Probabl y，你想为每个 [Name] 获得 SUM（[Interval]），但我只能猜测。 ..

您需要提供更多详细信息和更多示例数据。





请阅读我的评论...

I'm not sure what you're trying to achieve...

Probably, you want to get SUM([Interval]) for each [Name], but i can only guess...
You need to provide more details and more example data.


Please, read my comments...

SET DATEFORMAT dmy;
 
DECLARE @tmp TABLE ([ID] INT, [Name] VARCHAR(30), [Start Date] DATETIME, [End Date] DATETIME, Interval INT)
 
INSERT INTO @tmp ([ID], [Name], [Start Date], [End Date], Interval)
VALUES(1, 'Name1', '01/02/2013', '05/02/2013',0)
INSERT INTO @tmp ([ID], [Name], [Start Date], [End Date], Interval)
VALUES(2, 'Name1', '15/02/2013', '20/02/2013',10)
INSERT INTO @tmp ([ID], [Name], [Start Date], [End Date], Interval)
VALUES(3, 'Name1', '01/03/2013', '05/03/2013',9)
INSERT INTO @tmp ([ID], [Name], [Start Date], [End Date], Interval)
VALUES(4, 'Name1', '01/04/2013', '05/04/2013',27)
INSERT INTO @tmp ([ID], [Name], [Start Date], [End Date], Interval)
VALUES(5, 'Name1', '18/04/2013', '20/04/2013',13)
INSERT INTO @tmp ([ID], [Name], [Start Date], [End Date], Interval)
VALUES(6, 'Name1', '28/04/2013', '30/04/2013',8)
INSERT INTO @tmp ([ID], [Name], [Start Date], [End Date], Interval)
VALUES(7, 'Name1', '01/05/2013', '05/05/2013',1)
INSERT INTO @tmp ([ID], [Name], [Start Date], [End Date], Interval)
VALUES(8, 'Name1', '15/05/2013', '20/05/2013',10)
INSERT INTO @tmp ([ID], [Name], [Start Date], [End Date], Interval)
VALUES(9, 'Name1', '25/05/2013', '30/05/2013',5)

SELECT *
FROM @tmp
WHERE Interval <=10

结果： 1,2,3,6,7,8,9



记录号。 4（27天）和5（13天）不符合标准，因为间隔超过10天。

[/ EDIT]

Result: 1,2,3,6,7,8,9

Records no. 4 (27 days) and 5 (13 days) does not meet criteria because interval exceeds 10 days.
[/EDIT]

你是什么类型的输出寻找 ？获得结果集后，在代码中执行此操作可能最容易，但您没有说明您正在使用的语言或使用此结果集的方式。另一种选择是简单地让渲染层使用背景颜色，并在每次间隔> 1时改变颜色。 10天。这将导致以您描述的方式进行视觉分组。

What sort of output are you looking for ? It might be easiest to do this in code once you get your result set, but you don't say what language you're using or how this result set is being used. Another option is to simply have your rendering layer use a colour for the background and change that colour every time the interval is > 10 days. That would result in a visual grouping in the manner you're describing.

这篇关于获得连续记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！