0

I have some date data as follows:-

Person | Date
     1 |  1/1/2000
     1 |  6/1/2000
     1 | 11/1/2000
     1 | 21/1/2000
     1 | 28/1/2000

I need to delete rows within 14 days of a previous one. However, if a row is deleted, it should not later become a 'base' date against which later rows are checked. It's perhaps easier to show the results needed:-

Person | Date
     1 |  1/1/2000
     1 | 21/1/2000

My feeling is that recursive SQL will be needed but I'm not sure how to set it up. I'll be running this on Teradata.

Thanks.

--- Edit ---

Well, this is embarrassing. It turns out this question has been asked before - and it was asked by me! See this old question for an excellent answer from @dnoeth:-

Drop rows identified within moving time window

Community
  • 1
  • 1
rambles
  • 706
  • 5
  • 9

1 Answers1

1

Use recursive tables. Use ROWNUMBER() to Order and Number the dates.

DATEDIFF() to receive the number of days passed from previous date

Maybe SQL2012 and above can simplify using SUM() OVER PARTITION with a RANGE I didn't find it useful in this case

DECLARE @Tab TABLE  ([MyDate] SMALLDATETIME)
INSERT INTO @Tab ([MyDate])
VALUES
    ('2000-01-06'),
    ('2000-01-01'),
    ('2000-01-11'),
    ('2000-01-21'),
    ('2000-01-28')
;
WITH DOrder (MyDate, SortID) AS (
    SELECT MyDate,
           ROW_NUMBER() OVER (ORDER BY MyDate)SortID
    FROM @Tab t)

,Summarize(MyDate, SortID, sSum, rSum ) AS (
    SELECT MyDate, SortID, 0, 0 rSum
    FROM DOrder WHERE SortID = 1
    UNION ALL
    SELECT t.MyDate, t.SortID, DATEDIFF(D, ISNULL(s.MyDate,t.MyDate), t.MyDate) rSum,
           CASE WHEN DATEDIFF(D, ISNULL(s.MyDate,t.MyDate), t.MyDate) + s.rSum>14 THEN 0
                ELSE DATEDIFF(D, ISNULL(s.MyDate,t.MyDate), t.MyDate)
           END rSum
    FROM DOrder t INNER JOIN Summarize s 
         ON (t.SortID = s.SortID+1))

SELECT MyDate
FROM Summarize 
WHERE rSum=0
Yakov R.
  • 602
  • 8
  • 22