Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: finding records within set number of days

    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?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    DROP table DaTable
    create table DaTable(
    	id		int		not null,
    	begin_date	date	not null,
    	end_date	date	not null
    	constraint pk_DaTable primary key (id, begin_date), 
    	constraint BeginDate_LTE_EndDate CHECK (begin_date <= end_date), 
    )
    
    INSERT INTO DaTable (id, begin_date, end_date)
    SELECT 1234, CONVERT(DATE, '2009-10-09', 120), CONVERT(DATE, '2009-10-16', 120)
    UNION ALL
    SELECT 1234, CONVERT(DATE, '2009-12-16', 120), CONVERT(DATE, '2009-12-22', 120)
    UNION ALL
    SELECT 1234, CONVERT(DATE, '2009-12-28', 120), CONVERT(DATE, '2009-12-30', 120)
    UNION ALL
    SELECT 1234, CONVERT(DATE, '2010-01-13', 120), CONVERT(DATE, '2010-01-19', 120)
    UNION ALL
    SELECT 1234, CONVERT(DATE, '2010-04-29', 120), CONVERT(DATE, '2010-05-05', 120)
    
    SELECT * from DaTable
    
    WITH OrderedDates AS
    (
        SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID, begin_date) AS RowNum
              ,Id
              ,begin_date
              ,end_date
          FROM DaTable
    )
    
    SELECT ED.id, ED.begin_date, ED.end_date
    FROM OrderedDates as SD
    	INNER JOIN OrderedDates as ED ON
    		SD.id = ED.Id AND
    		SD.RowNum = ED.RowNum - 1 AND
    		ED.begin_date BETWEEN SD.end_date AND DATEADD(dd, 30, SD.end_date)
    ORDER BY ED.id, ED.begin_date
    Edit: Added PARTITION BY Id. Strictly not needed, but perhaps easier to understand.
    Last edited by Wim; 08-14-10 at 19:54.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •