SQL - View - Look forward 1 record

I have a table with EventID & Evt_StartDate.   I want to look forward 1 row and say the Evt_StopDate is the next record Evt_StartDate (based on EventID).  Also I'd like the the last record (in this case EventID = 133) to put in today's date as Evt_Stop Date.

EventID         Evt_StartDate   Evt_StopDate
133                           3/4/2015            4/14/2015
125                  2/25/2015            3/4/2015
124                  2/20/2015            2/25/2015
123                  2/19/2015            2/20/2015


Thanks in advance
LJG
LVL 2
LJGAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use a CTE with ROW_NUMBER() function to achieve this:
WITH CTE_Events (RowNumber, EventID, StartDate)
AS (
	SELECT ROW_NUMBER() OVER (ORDER BY EventID DESC), EventID, Evt_StartDate
	FROM MyEventsTableName)
SELECT E1.EventID, E1.StartDate, ISNULL(E2.StartDate,GETDATE()) AS StopDate
FROM CTE_Events E1
	LEFT JOIN CTE_Events E2 ON E1.RowNumber = E2.RowNumber+1

Open in new window

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
It is not clear what is that you want the query to do. It will be helpful if you could explain  using some of the data from you example.

What you will supply (as criteria) to get what?
0
 
LJGAuthor Commented:
If I have a table with 2 fields (for this sake of this question - 4 records)

EventID         Evt_StartDate  
133                  3/4/2015  
129                  2/25/2015  
127                  2/20/2015  
123                  2/19/2015  

I would like to create a View or proc that each record create a calculated field Evt_StopDate  

The Evt_Stop Date would be the Evt_StartDate of the next record.
eg record 123 pulls Evt_StopDate from from record 127 --> Evt_StartDate
     record 127 pulls Evt_StopDate from from record 129 --> Evt_StartDate

      View Results:
EventID         Evt_StartDate   Evt_StopDate
133                  3/4/2015            4/14/2015
129                  2/25/2015            3/4/2015
127                  2/20/2015            2/25/2015
123                  2/19/2015            2/20/2015
0
 
Éric MoreauSenior .Net ConsultantCommented:
Which version of SQL server? SQL 2012 offers LEAD that looks ahead to give you the value (or LEAD for previous rows)
0
 
LJGAuthor Commented:
Vitor
Thanks so much - your SQL is perfect for what I'm looking for - I wish I could give you an A+ .

I'm working in SQL Server 2008 R2 and have never used Common Table Expressions (CTE) before.  Something I need to learn.

Thanks again
LJG
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.