Can I replace this loop in a sql server query -

i have list of items, sorted date descending, , checks them this:

counted = 0 datetocheck =    foreach(item) {    if( abs(item.date - datetocheck) > 14 days )    {       counted++    }    datetocheck = item.date } 

the goal count of items on list did not occur within 14 days of previous item.

the table list of dates, this:

 index      itemdate   307000    2017-08-17   307001    2017-04-25    307002    2016-09-23   307003    2016-08-26   307004    2016-04-30   307005    2016-03-01   307006    2016-03-01 

the result here should count of 6, last 1 ignored since within 14 days of 1 before.

you can use query if not have id column. use id column directly if have one.

;with tbl (  select row_number() over(order itemdate asc) id, itemdate table_name  ) select count(a.itemdate) tbl inner join tbl b on b.id = a.id + 1 datediff(d, a.createdon, b.createdon) > 14;  

with id column, query changes

select count(a.itemdate) table_name inner join table_name b on b.id = a.id + 1 datediff(d, a.createdon, b.createdon) > 14;