How do I compare two rows within the same table?

I have a table with memberid, session # and session date
How can I identify the row that is at least X days after the previous?

LVL 2
ExtremeFitnessAsked:
Who is Participating?
 
YveauCommented:
This will result in what you asked for. Every record that is at least @diff days after the mentioned record:

create table #Y ([ID] int, [date] datetime)
go

insert into #Y values (1, '20070101')
insert into #Y values (2, '20070102')
insert into #Y values (3, '20070103')
insert into #Y values (4, '20070104')
insert into #Y values (5, '20070105')
insert into #Y values (6, '20070106')
insert into #Y values (7, '20070107')
insert into #Y values (8, '20070108')

declare @diff int
select  @diff = 3

select  *
from    #Y Y1
inner   join #Y Y2
on      Y2.[Date] >= dateadd(dd, @diff, Y1.[Date])
order   by Y1.[ID], Y2.[ID]

-->> result:
ID          date                    ID          date
----------- ----------------------- ----------- -----------------------
1           2007-01-01 00:00:00.000 4           2007-01-04 00:00:00.000
1           2007-01-01 00:00:00.000 5           2007-01-05 00:00:00.000
1           2007-01-01 00:00:00.000 6           2007-01-06 00:00:00.000
1           2007-01-01 00:00:00.000 7           2007-01-07 00:00:00.000
1           2007-01-01 00:00:00.000 8           2007-01-08 00:00:00.000
2           2007-01-02 00:00:00.000 5           2007-01-05 00:00:00.000
2           2007-01-02 00:00:00.000 6           2007-01-06 00:00:00.000
2           2007-01-02 00:00:00.000 7           2007-01-07 00:00:00.000
2           2007-01-02 00:00:00.000 8           2007-01-08 00:00:00.000
3           2007-01-03 00:00:00.000 6           2007-01-06 00:00:00.000
3           2007-01-03 00:00:00.000 7           2007-01-07 00:00:00.000
3           2007-01-03 00:00:00.000 8           2007-01-08 00:00:00.000
4           2007-01-04 00:00:00.000 7           2007-01-07 00:00:00.000
4           2007-01-04 00:00:00.000 8           2007-01-08 00:00:00.000
5           2007-01-05 00:00:00.000 8           2007-01-08 00:00:00.000

Hope this helps ...
0
 
SQL_SERVER_DBACommented:
join the table on itself.
0
 
ExtremeFitnessAuthor Commented:
Figured it out.. Much faster than a join. :)


SELECT CustomerId,
      CAST(Session_date AS CHAR(11)) AS 'SessDate',
      CAST(NextDate AS CHAR(11)) AS NextDate,
      DATEDIFF(DAY, Session_date, NextDate)
FROM (
      SELECT DISTINCT PT.CustomerId, PT.Session_Date,
            (SELECT MIN(Session_Date)
                  FROM Personal_Training1 AS PTMin
                  WHERE PTMin.Session_Date > PT.Session_Date AND PTMIN.CustomerId = PT.CustomerId) AS 'NextDate'
      FROM Personal_Training1 AS PT
      WHERE CustomerId = 31790
) AS PTCheck
ORDER BY Session_date

Thanks for the help.  :)
0
 
YveauCommented:
Glad I could be of any help ... but your solution is a self join as well :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.