?
Solved

How do I compare two rows within the same table?

Posted on 2007-10-15
4
Medium Priority
?
9,612 Views
Last Modified: 2012-06-21
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
Comment
Question by:ExtremeFitness
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
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

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

Author Comment

by:ExtremeFitness
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

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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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.

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question