Solved

# How do I compare two rows within the same table?

Posted on 2007-10-15
Medium Priority
9,612 Views
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?

0
Question by:ExtremeFitness
• 2

LVL 18

Accepted Solution

Yveau earned 1000 total points
ID: 20078394
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

LVL 16

Assisted Solution

SQL_SERVER_DBA earned 1000 total points
ID: 20078460
join the table on itself.
0

LVL 2

Author Comment

ID: 20078734
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

LVL 18

Expert Comment

ID: 20078774
Glad I could be of any help ... but your solution is a self join as well :-)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
###### Suggested Courses
Course of the Month8 days, left to enroll