SQL Group with Min and Max

Question

I have a super simple query using group by and I just can't figure out how to get the desired result. It's literally a simple query with min() and max(). I have a table where assets belong to a certain location with a date in/out (it also has multiple in/out dates without changing location), but if an asset moves back to location where its previously already been, the grouping doesn't work. I tried using a combination of over(partition by...), just can't solve it.

Table:

Asset   Location    Date In     Date Out
------------------------------------------
00001   A           01/01/2020  13/01/2020
00001   A           14/01/2020  26/01/2020
00001   A           27/01/2020  08/02/2020
00001   B           09/02/2020  21/02/2020
00001   B           22/02/2020  05/03/2020
00001   B           06/03/2020  18/03/2020
00001   A           19/03/2020  31/03/2020
00001   A           01/04/2020  13/04/2020
00001   A           14/04/2020  26/04/2020
00001   A           27/04/2020  09/05/2020
00001   A           10/05/2020  16/09/2020

Desired result:

Asset   Location    Date In     Date Out
------------------------------------------
00001   A           01/01/2020  08/02/2020
00001   B           09/02/2020  18/03/2020
00001   A           19/03/2020  16/09/2020

Actual result:

Asset   Location    Date In     Date Out
------------------------------------------
00001   A           01/01/2020  16/09/2020
00001   B           09/02/2020  18/03/2020

Show us the query that you tried – Ankit Das Sep 16 '20 at 14:47 — Ankit Das, Sep 16 '20 at 14:47

score 2 · Accepted Answer · answered Sep 16 '20 at 14:47

This is a groups-and-islands problem. A simple solution is the difference of row numbers:

select asset, location, min(date_in), max(date_out)
from (select t.*,
             row_number() over (partition by asset order by date_in) as seqnum             
             row_number() over (partition by asset, location order by date_in) as seqnum_2
      from t
     ) t
group by asset, location, (seqnum - seqnum_2);

Why this works is a little tricky to explain. If you look at the subquery you'll see how the difference in row numbers defines the consecutive rows you want.

I understand it totally. Exactly what I was after. Thanks a lot! There is so much for me to learn... — Linear_D, Sep 16 '20 at 15:01

SQL Group with Min and Max

1 Answers1