SQL - View - Look forward 1 record

LJG
LJG used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike EghtebasDatabase and Application Developer

Commented:
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?
LJG

Author

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 Consultant
Top Expert 2016

Commented:
Which version of SQL server? SQL 2012 offers LEAD that looks ahead to give you the value (or LEAD for previous rows)
IT Engineer
Distinguished Expert 2017
Commented:
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

LJG

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial