Find Difference between end dates by comparing rows

i have a table

----------
User
----------
userID(pk)
startdate // update : i am not using this field.
enddate

i need to compare between the end_dates between the rows comparing whether it is more than 3 days and count the userid.

i am doing something similar to this

WHILE @@FETCH_STATUS = 0
BEGIN

   select @lastrowID = max(rowid) from @User
   if (@userid  = (select userId from @User where rowid = @lastRowID))
   begin
       update @User set NextEndDate= @endDate where rowid = @lastRowID and userid = @userid
   end
   else
   begin
       insert @UserTable (userid, EndDate,NextEndDate) values (@userid, @endDate,@NextEndDate)
   end
END

my idea is to loop around the table and create a nextend date and then find the comparing using datediff. i am stuck with the first part of creating nextend date and i believe the 2nd part of comparing would be easy. i am stuck in between.

my question is am i d开发者_StackOverflow社区oing the right thing, its getting pretty complicated. i am confused whether to get the result through sql query or use C# at code behind using Linq or something similar.

Update: sorry If i wasn't clear in explaining my scenario : i am trying to find the count, no of times a client has visited. ex: userid: 1 have may a visited daily or once in a month. so i need to get the count(frequency of the user visit). so if the users visited end date was

userid     enddate
1          1/1/2010
1          1/2/2010  count 1
1          1/10/2010 count 2 difference is more than 3 days
1          1/13/2010 count 2 ( because diff is less than 3 days)

thats how i should count, thats y i was trying to use cursor which was too complicate for me to solve. I appreciate for your guidance.

Ok, I understand your problem now. I know there is a better way to do this in SQL, maybe with CTEs, but this solution should work and doesn't use cursors. This will give you a full table with the datediff of the previous enddate (where applicable). You can then select from it based on the datediff.

select u1.*, datediff(day, u2.enddate, u1.enddate) as days from
    (
    select userid, enddate, row_number() over(partition by userid order by userid, enddate) as rownumber
    from [user]
    ) u1
    left join 
    (
    select userid, enddate, row_number() over(partition by userid order by userid, enddate) as rownumber
    from [user]
    )u2
    on u1.userid = u2.userid
    and u1.rownumber = u2.rownumber + 1

EDIT

declare @table table (userid int, startdate datetime, enddate datetime)

insert into @table (userid, startdate, enddate) values (1, '01-JAN-2010', '2-JAN-2010')
insert into @table (userid, startdate, enddate) values (2, '01-JAN-2010', '3-JAN-2010')
insert into @table (userid, startdate, enddate) values (3, '01-JAN-2010', '4-JAN-2010')
insert into @table (userid, startdate, enddate) values (4, '01-JAN-2010', '5-JAN-2010')
insert into @table (userid, startdate, enddate) values (5, '01-JAN-2010', '6-JAN-2010')
insert into @table (userid, startdate, enddate) values (6, '01-JAN-2010', '7-JAN-2010')
insert into @table (userid, startdate, enddate) values (7, '01-JAN-2010', '8-JAN-2010')

select SUM(yn) as dueinmorethanthreedays from 
  (select 
    (case when DATEADD(day,3,startdate) < enddate then 1 else 0 end) as yn 
    from @table
  ) as derived

A subquery which returns 1 for each row where the startdate is less than three days of the enddate (and 0 otherwise) can be summed to get the total.