Eliminating rows by date range

Question

I have the following:

Index   dateOfInquiry   
649454      2016-02-05 
649455      2016-02-05   

And I've got this query:

SELECT COUNT(a.dateOfInquiry) as NumberRecords
FROM 
( 
    SELECT ROW_NUMBER() OVER(ORDER BY dateOfInquiry ASC) 
        Id, dateOfInquiry

    FROM 
    Table
) a 

INNER JOIN 
(
    SELECT ROW_NUMBER() OVER(ORDER BY dateOfInquiry ASC) 
            Id, dateOfInquiry

    FROM 
        Table
) 
b 

ON b.ID = a.ID + 1 
WHERE ABS( DATEDIFF(d, a.dateOfInquiry, b.dateOfInquiry ) ) > 14
GROUP BY a.strTransactionID 

What I'm trying to do is only return 1 record per 14 day period where they exist. So I want 1 in the dataset above, but I'm getting 0 (it eliminates both records since there isn't 14 days between them)

Also, it gets more complicated if there are more records and the behavior isn't quite right. If I had a record on the 1st, the 2nd, 3rd and so on through the 15th, I'd still want only 1 record, then start counting again at the next record at the 16th or after, ignoring further records until the difference is more than 14 days.

Essentially I want to count a record as 1, then ignore all further records until 14 days are up.

Another Sample and the result I'd want is 2:

Index   dateOfInquiry   
649454      2016-02-01  <- count
649455      2016-02-12  -ignore (<l4 past 649454)
649456      2016-02-12  -ignore (<l4 past 649454)
649457      2016-02-17  <- count
649458      2016-02-22  -ignore (<l4 past 649457)
649459      2016-02-25  -ignore (<l4 past 649457)

Answer 1

using outer apply() to get the id of the next qualifying row for each row, and using a recursive common table expression to start at the beginning and work your way forward:

;with cte as (
  select t.id, t.dateOfInquiry, x.next_id
  from t
    outer apply (
      select top 1 next_id = i.id
      from t as i
      where i.dateOfInquiry > dateadd(day,14,t.dateOfInquiry)
      order by dateofInquiry, id asc
    ) x
)
, r_cte as (
    --anchor row(s) / starting row(s)
  select 
      id
    , dateOfInquiry
    , next_id
  from cte t
  where not exists (
    select 1
    from cte as i
    where i.id < t.id
    )
  union all
  --recursion starts here
  select 
      c.id
    , c.dateOfInquiry
    , c.next_id
  from cte c
    inner join r_cte p
      on c.id = p.next_id
)
select id, dateOfInquiry
from r_cte

rextester demo: http://rextester.com/PIMVPM32168

returns:

+--------+---------------+
|   id   | dateOfInquiry |
+--------+---------------+
| 649454 | 2016-02-01    |
| 649457 | 2016-02-17    |
+--------+---------------+

Answer 2

what is the use of index column in you requirement ?

you should provide real like scenario.

Try my script with other sample data.I have use 2 ROW_NUMBER function inside recursive CTE.So suppose if no better solution than this comes up then I think CURSOR will be a better and understandable approach.

declare @t table(Index1 int,dateOfInquiry  datetime) 
insert into @t VALUES
 (649454,'2016-02-01')  --<- count
,(649455,'2016-02-12')  ---ignore (<l4 past 649454)
,(649456,'2016-02-12')  ---ignore (<l4 past 649454)
,(649457,'2016-02-17')  --<- count
,(649458,'2016-02-22')  ---ignore (<l4 past 649457)
,(649459,'2016-03-02')  ---count 
,(649459,'2016-03-15')  ---ignore (<l4 past 649457)

;WITH CTE
AS (
    SELECT min(dateOfInquiry) dateOfInquiry
        ,cast(0 AS BIGINT) r
    FROM @t

    UNION ALL

    SELECT *
    FROM (
        SELECT t.dateOfInquiry
            ,ROW_NUMBER() OVER (
                ORDER BY t.dateOfInquiry
                ) rn1
        FROM @t t
        INNER JOIN (
            SELECT dateOfInquiry
            FROM (
                SELECT dateOfInquiry
                    ,row_number() OVER (
                        ORDER BY dateOfInquiry DESC
                        ) rn
                FROM cte c1
                ) c2
            WHERE rn = 1
            ) c1 ON datediff(day, c1.dateOfInquiry, t.dateOfInquiry) >= 14
        ) t4
    WHERE rn1 = 1
    )
SELECT *
FROM cte