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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
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
É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)
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use a CTE with ROW_NUMBER() function to achieve this:
WITH CTE_Events (RowNumber, EventID, StartDate)
AS (
	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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LJGAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.