select records within 30 days of previous records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

select records within 30 days of previous records

Author

Topic

scabral7
Yak Posting Veteran

57 Posts

Posted - 2010-08-13 : 11:43:52

I have a table with a list of patient id's and there begin date and end dates as follows:

ID begin_date end_date
1234 10/09/2009 10/16/2009
1234 12/16/2009 12/22/2009
1234 12/28/2009 12/30/2009
1234 01/13/2010 01/19/2010
1234 04/29/2010 05/05/2010

what i need to do is select the records that have an end_date within 30 days of a begin_date. So in the above example, i would need to get the following records:

ID begin_date end_date
1234 12/28/2009 12/30/2009
1234 01/13/2010 01/19/2010

Because 12/28/2009 is 30 days within 12/22/2009 (previous end_date) and 01/13/2010 is within 30 days of 12/30/2009 (previous end_date) and 12/22/2009 (2nd previous end_date).

is there a good way to do this using T-SQL?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-13 : 11:52:33

Yes., Use Cross Apply.

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 02:51:21

[code]SELECT t.ID,t.begin_date.t.end_date
FROM Table t
CROSS APPLY (SELECT COUNT(*) AS cnt
FROM Table
WHERE ID = t.ID
AND end_date>= DATEADD(dd,-30,t.begin_date)t1
WHERE t1.cnt>0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-08-17 : 12:31:42

The previous example doesn't work. The cross apply isn't exactly what you want. This type of comapirson is usually done with a CTE

declare @t table (Id int, begin_date smalldatetime, end_date smalldatetime)

insert into @t
select 1234,'10/09/2009','10/16/2009' union all
select 1234,'12/16/2009','12/22/2009' union all
select 1234,'12/28/2009','12/30/2009' union all
select 1234,'01/13/2010','01/19/2010' union all
select 2345,'10/09/2009','10/16/2009' union all
select 2345,'12/16/2009','12/22/2009' union all
select 1234,'04/29/2010','05/05/2010'

;with cte
as (select ID, begin_date, end_date,
Row_Number() over (partition by id order by id, begin_date, end_date) RowID
from @t)

select NextRow.ID, NextRow.Begin_date, NextRow.End_Date
from cte CurrRow
left outer join cte NextRow
on CurrRow.ID = NextRow.ID
and CurrRow.RowID = NextRow.RowID - 1
where dateDiff(dd, CurrRow.End_Date, NextRow.Begin_Date) <= 30

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-17 : 12:53:34

[code]DECLARE @Sample TABLE
(
ID INT,
BeginDate DATETIME,
EndDate DATETIME
)

INSERT @Sample
VALUES (1234, '10/09/2009', '10/16/2009'),
(1234, '12/16/2009', '12/22/2009'),
(1234, '12/28/2009', '12/30/2009'),
(1234, '01/13/2010', '01/19/2010'),
(1234, '04/29/2010', '05/05/2010')

-- Visakh (Scan count 6, logical reads 6)
SELECT t.ID,
t.BeginDate,
t.EndDate
FROM @Sample AS t
CROSS APPLY (
SELECT COUNT(*) AS cnt
FROM @Sample AS x
WHERE x.ID = t.ID
AND EndDate >= DATEADD(DAY, -30, t.BeginDate)
) AS t1
WHERE t1.cnt > 0

-- MSquared (Scan count 6, logical reads 6)
;WITH cte
AS (
SELECT ID,
BeginDate,
EndDate,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID, BeginDate, EndDate) AS RowID
FROM @Sample
)
SELECT NextRow.ID,
NextRow.BeginDate,
NextRow.EndDate
FROM cte AS CurrRow
LEFT JOIN cte AS NextRow ON NextRow.ID = CurrRow.ID
AND NextRow.RowID - 1 = CurrRow.RowID
WHERE DATEDIFF(DAY, CurrRow.EndDate, NextRow.BeginDate) <= 30

-- Peso (Scan count 2, logical reads 6)
SELECT s.ID,
s.BeginDate,
s.EndDate
FROM @Sample AS s
WHERE EXISTS(SELECT * FROM @Sample AS x WHERE x.EndDate BETWEEN DATEADD(DAY, -30, s.BeginDate) AND s.BeginDate)[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources