Query to calculate start and end dates from a list of only start dates

Question

I have data in the following form that needs to be combined such that I create a single row from two rows of the input data where the second column is the value of the next row less one day.

I've struggled trying to come up with a way to do this, but not gotten very far. I hope this illustration explains the problem:

Table structure

CREATE TABLE Process(
    ProcessOrder int NOT NULL IDENTITY (1, 1),
    ProcessDate date NOT NULL
);

Query

SELECT ProcessOrder, ProcessDate From Process ORDER BY ProcessOrder;

Data

ProcessOrder  ProcessDate
------------  -----------
208           2016-01-04
209           2016-01-11
210           2016-01-18
211           2016-01-25
212           2016-02-01
213           2016-02-08

I need to get the above data into the following shape (note that ProcessEnd is one day less than ProcessBegin from the subsequent record):

ProcessOrder  ProcessBegin  ProcessEnd
------------  ------------  ----------
208           2016-01-04    2016-01-10
209           2016-01-11    2016-01-17
210           2016-01-18    2016-01-24
211           2016-01-25    2016-01-31
212           2016-02-01    2016-02-07
213           2016-02-08    *NULL*

EDIT

I am guaranteed that values in the ProcessOrder column are consecutive with no missing values.

Answer 1

Since you're on 2008, you don't have the LEAD function yet (I believe), so you can do it yourself with a CTE:

WITH RankedProcess AS
(
  SELECT ProcessOrder, ProcessDate, ROW_NUMBER() OVER(ORDER BY ProcessDate) AS Seq
  FROM Process
)
SELECT P1.ProcessOrder, P1.ProcessDate AS ProcessBegin, DATEADD(day, -1, P2.ProcessDate) AS ProcessEnd
FROM RankedProcess AS P1
  LEFT OUTER JOIN RankedProcess AS P2
    ON P1.Seq = P2.Seq - 1

In 2012 and later, you can just use LEAD:

SELECT ProcessOrder, ProcessDate AS ProcessBegin, LEAD(DATEADD(day, -1, ProcessDate)) OVER(ORDER BY ProcessDate) AS ProcessEnd
FROM Process

Given the update that ProcessOrder is guaranteed to be consecutive with no missing values (and I'm assuming they are in the same order as the dates), you can get away without the CTE and just use the ProcessOrder column. However, the solution with the CTE is more general, which I think is still useful since I am generally skeptical of data requirements that aren't enforced by the database.

Answer 2

If you know that ProcessOrder will be consecutive, you can just do a self-join:

SELECT p_begin.ProcessOrder, p_begin.ProcessDate as ProcessBegin,
    DateAdd(day, -1, p_end.ProcessDate) as ProcessEnd
FROM Process p_begin
LEFT OUTER JOIN Process p_end
    ON p_end.ProcessOrder = p_begin.ProcessOrder + 1

I did a left outer join so any missing numbers will result in a NULL ProcessEnd, instead of a missing row.

Answer 3

You can simulate LEAD function to get next record Date and DATEADD to subtract one day.

SELECT    p1.ProcessOrder,
          p1.ProcessDate as ProcessBegin,
          DATEADD(DAY, -1, p2.ProcessDate) as ProcessEnd
FROM      Process p1
LEFT JOIN Process p2
ON        p2.ProcessOrder = p1.ProcessOrder + 1
GO

ProcessOrder | ProcessBegin        | ProcessEnd         
-----------: | :------------------ | :------------------
         208 | 04/01/2016 00:00:00 | 10/01/2016 00:00:00
         209 | 11/01/2016 00:00:00 | 17/01/2016 00:00:00
         210 | 18/01/2016 00:00:00 | 24/01/2016 00:00:00
         211 | 25/01/2016 00:00:00 | 31/01/2016 00:00:00
         212 | 01/02/2016 00:00:00 | 07/02/2016 00:00:00
         213 | 08/02/2016 00:00:00 | null               

dbfiddle here