CODEWITHSUNDEEP
sqlt-sql
ssokol91
ssokol91

Reputation: 562

Creating Start/Stop date ranges dataset with multiple starts for one stop

I am trying to create a dataset with all Starts to the nearest Stop. The problem is that number of starts between the 1st Start and the next Stop varies.

The source dataset looks like this:

RowNum Timestamp        Action 
==============================
   1   01/01/18 12:00   Start 
   2   01/01/18 01:00   Start
   3   01/01/18 02:00   Stop
   4   01/01/18 03:00   Start
   5   01/01/18 05:00   Stop
   6   01/01/18 13:00   Start
   7   01/01/18 15:00   Start
   8   01/01/18 17:00   Start
   9   01/01/18 21:00   Stop

I want my final outcome to be something like this:

Start           Stop
================================
01/01/18 12:00  01/01/18 02:00
01/01/18 03:00  01/01/18 05:00
01/01/18 13:00  01/01/18 21:00

Or even if it's there is a record for every Start to the nearest Stop that would be great as well.

Thank you so much for any guidance.

Upvotes: 1

Views: 125

Answers (4)

Dávid Laczkó
Dávid Laczkó

Reputation: 1101

Solution plan:
Scenario A is when you want to see a Stop for all Starts: find the earliest Stop that is after a Start.
Scenario B is when you want to see a Stop for the earliest/latest Start only: first get a set from the result of Senario A, then find the earliest/latest Start that is before a Stop.
This solution does not consider when there are duplicates and you want to keep them in the result - that would involve a third field like RowNum.
One possible implementation: 

DECLARE @Table TABLE (
    Timestamp DATETIME,
    Action VARCHAR(5)
    )

INSERT @Table
VALUES
   ('01/01/18 12:00', 'Start'),
   ('01/01/18 01:00', 'Start'),
   ('01/01/18 02:00', 'Stop'),
   ('01/01/18 03:00', 'Start'),
   ('01/01/18 05:00', 'Stop'),
   ('01/01/18 13:00', 'Start'),
   ('01/01/18 15:00', 'Start'),
   ('01/01/18 17:00', 'Start'),
   ('01/01/18 21:00', 'Stop'),
   ('01/01/18 22:00', 'Start')

SELECT * FROM @Table WHERE Action = 'Start' ORDER BY Timestamp
SELECT * FROM @Table WHERE Action = 'Stop' ORDER BY Timestamp


-- Scenario A:
SELECT Starts.Timestamp as Start, MIN (Stops.Timestamp) as Stop
FROM
(SELECT * FROM @Table WHERE Action = 'Start') as Starts
LEFT OUTER JOIN
(SELECT * FROM @Table WHERE Action = 'Stop') as Stops
on Stops.Timestamp >= Starts.Timestamp
GROUP BY Starts.Timestamp
ORDER BY Starts.Timestamp

-- Scenario B:
-- same block as above with a temp table to hold the results
SELECT Starts.Timestamp as Start, MIN (Stops.Timestamp) as Stop
INTO #allstops
FROM
(SELECT * FROM @Table WHERE Action = 'Start') as Starts
LEFT OUTER JOIN
(SELECT * FROM @Table WHERE Action = 'Stop') as Stops
on Stops.Timestamp >= Starts.Timestamp
GROUP BY Starts.Timestamp

SELECT allstops.Start, LatestStart.Stop
FROM #allstops as allstops
LEFT OUTER JOIN (
    SELECT MIN (Start) as Start, Stop -- this returns the earliest Start, switch to MAX to get the latest
    FROM #allstops
    GROUP BY Stop
    ) as LatestStart
on allstops.Start = LatestStart.Start

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

You can use lag and ceiling functions as :

select max(Start) as Start, max(Stop) as Stop
  from
  (
    select row_number() over ( order by rownum ) as rn,
           ( case when action = 'Start' then q.timestamp end ) as Start,
           ( case when action = 'Stop' then q.timestamp end ) as Stop
      from
    (
    select t.*,       
           lag(action) over (order by rownum) as lg
      from tab t
    ) q
    where q.action != coalesce(lg,'Stop')
   ) r
  group by ceiling(rn*.5)   
  order by ceiling(rn*.5);

        Start                 Stop
01.01.2018 12:00:00   01.01.2018 02:00:00
01.01.2018 03:00:00   01.01.2018 05:00:00
01.01.2018 13:00:00   01.01.2018 21:00:00

Rextester Demo

P.S. For every binary pair of steps we determine START and STOP rows where one of them non-null, while the other member of pair is null. Because of this logic I needed mod(...,2) for row numbers and satisfied that by ceil(rn*.5) which yields 1 for both 1 or 2, 2 for both 3 and 4, and 3 for 5 and 6, respectively, i.e. nearest consecutive upper integer.

Upvotes: 1

dnoeth
dnoeth

Reputation: 60502

You can utilize a Cumulative Min to find the next "Stop" event:

select *,
   min(case when Action = 'Stop' then Timestamp end) -- next Stop
   over (--partition by ???
         order by Timestamp
         rows between current row and unbounded following) as Stop
from tab

Based on that it's a simple aggregation:

with cte as
 ( select *,
      min(case when Action = 'Stop' then Timestamp end) -- next Stop
      over (--partition by ???
            order by Timestamp
            rows between current row and unbounded following) as Stop
   from tab
 )
select
   min(Timestamp) as start, 
   Stop
from cte
group by Stop
order by 1

Upvotes: 2

linjoehan
linjoehan

Reputation: 103

Firstly Get a stop for every start in the table

select
A.Timestamp as Start
min(B.Timestamp) as Stop
from
  mytable A 
left join 
  mytable B
    on A.Action = 'Start' 
    and B.Action = 'Stop'
    and A.Timestamp < B.Timestamp
group by A.Timestamp

You can then use that result (Alias table R1 int the below query) to get your final table

select
min(Start),
Stop
from
(
select
A.Timestamp as Start
min(B.Timestamp) as Stop
from
  mytable A 
left join 
  mytable B
    on A.Action = 'Start' 
    and B.Action = 'Stop'
    and A.Timestamp < B.Timestamp
group by A.Timestamp
) as R1
group by Stop

Upvotes: 1

Related Questions