How can I get the differences between the OrderStop date and the OrderStart date in the next column?

Question

How can I get the differences between the OrderStop and the OrderStart in the next column?

For example, row 1 has OrderStop date of 1/31/2007 I want to count the days between the next start date 1/26/2007 I know they are overlaps.

ID  OrderStart  OrderStop
132 4/14/2006   1/31/2007
132 1/26/2007   3/14/2007
132 2/1/2007    3/2/2007
132 3/2/2007    3/14/2007
132 3/14/2007   1/8/2010
132 11/26/2008  1/20/2011
132 1/8/2010    7/14/2010
132 7/14/2010   8/15/2012
132 8/15/2012   1/17/2013
132 1/17/2013   3/22/2013
132 3/21/2013   5/2/2013
132 5/2/2013    8/2/2013
132 5/22/2013   8/2/2013
132 7/29/2013   3/6/2014
132 3/5/2014    7/16/2014
132 7/16/2014   6/19/2015
132 8/21/2014   6/19/2015
132 6/19/2015   4/1/2016
132 6/25/2015   9/9/2015
132 4/1/2016    5/3/2016
132 5/3/2016    7/27/2016
132 8/15/2016   11/2/2016

I am trying to accomplished the below. How can I create a SQL statement that can accomplish this?

132 4/14/2006   1/31/2007
132 1/26/2007   4/1/2016
132 4/1/2016    7/27/2016
132 8/15/2016   11/2/2016

plz clean up the attached, read FAQ on how to ask a question. Give us the SQL you've tried so far — EoinS, Dec 05 '17 at 16:56
Possible duplicate of [Get previous and next row from rows selected with (WHERE) conditions](https://stackoverflow.com/questions/27086377/get-previous-and-next-row-from-rows-selected-with-where-conditions) or many other questions about `lag()`/`lead()` — underscore_d, Dec 05 '17 at 16:59
I don't think this is possible with how this is laid out. Is there a rec_id field that makes each of those rows unique? — briskovich, Dec 05 '17 at 17:12
Your criteria for achieving your second data set are unclear and don't appear to have anything to do with the difference between OrderStop and OrderStart. — Sentinel, Dec 05 '17 at 17:22

score 1 · Answer 1 · answered Dec 05 '17 at 18:34

Consumable sample data makes things much easier for us. So does the version of SQL server. Below is a solution that uses 2012's LEAD functionality. The second one is for pre-2012 systems; it accomplishes the same thing but requires a self join and will not be as efficient.

declare @orders table (id int, orderStart date, orderStop date);
insert @orders
values
(132,'4/14/2006 ','1/31/2007'),
(132,'1/26/2007 ','3/14/2007'),
(132,'2/1/2007  ','3/2/2007 '),
(132,'3/2/2007  ','3/14/2007'),
(132,'3/14/2007 ','1/8/2010 '),
(132,'11/26/2008','1/20/2011'),
(132,'1/8/2010  ','7/14/2010'),
(132,'7/14/2010 ','8/15/2012'),
(132,'8/15/2012 ','1/17/2013'),
(132,'1/17/2013 ','3/22/2013'),
(132,'3/21/2013 ','5/2/2013 '),
(132,'5/2/2013  ','8/2/2013 '),
(132,'5/22/2013 ','8/2/2013 '),
(132,'7/29/2013 ','3/6/2014 '),
(132,'3/5/2014  ','7/16/2014'),
(132,'7/16/2014 ','6/19/2015'),
(132,'8/21/2014 ','6/19/2015'),
(132,'6/19/2015 ','4/1/2016 '),
(132,'6/25/2015 ','9/9/2015 '),
(132,'4/1/2016  ','5/3/2016 '),
(132,'5/3/2016  ','7/27/2016'),
(132,'8/15/2016 ','11/2/2016');

select *, 
  nextStart   = lead(orderStart,1) over (order by orderStart),
  daysBetween = abs(datediff(day,lead(orderStart,1) over (order by orderStart), orderStop))
from @orders
order by orderStart;


with preSort as
(
  select *, rn = row_number() over (order by orderstart)
    from @orders
)
select p2.id, p2.orderStart, p2.orderStop , nextStart = p1.orderStart, 
  daysBetween = abs(datediff(day, p2.orderStop, p1.orderStart))
from preSort p1 join preSort p2 on p1.rn = p2.rn+1
order by p1.orderStart;

Both Return

id          orderStart orderStop  nextStart  daysBetween
----------- ---------- ---------- ---------- -----------
132         2006-04-14 2007-01-31 2007-01-26 5
132         2007-01-26 2007-03-14 2007-02-01 41
132         2007-02-01 2007-03-02 2007-03-02 0
132         2007-03-02 2007-03-14 2007-03-14 0
132         2007-03-14 2010-01-08 2008-11-26 408
132         2008-11-26 2011-01-20 2010-01-08 377
132         2010-01-08 2010-07-14 2010-07-14 0
132         2010-07-14 2012-08-15 2012-08-15 0
132         2012-08-15 2013-01-17 2013-01-17 0
132         2013-01-17 2013-03-22 2013-03-21 1
132         2013-03-21 2013-05-02 2013-05-02 0
132         2013-05-02 2013-08-02 2013-05-22 72
132         2013-05-22 2013-08-02 2013-07-29 4
132         2013-07-29 2014-03-06 2014-03-05 1
132         2014-03-05 2014-07-16 2014-07-16 0
132         2014-07-16 2015-06-19 2014-08-21 302
132         2014-08-21 2015-06-19 2015-06-19 0
132         2015-06-19 2016-04-01 2015-06-25 281
132         2015-06-25 2015-09-09 2016-04-01 205
132         2016-04-01 2016-05-03 2016-05-03 0
132         2016-05-03 2016-07-27 2016-08-15 19
132         2016-08-15 2016-11-02 NULL       NULL

How can I get the differences between the OrderStop date and the OrderStart date in the next column?

1 Answers1