Hey Ray,
On Tue, Aug 11, 2026 at 2:33 PM Ray O'Donnell <[email protected]> wrote:
> Hi all,
>
> I need some help constructing a query...
>
> aircraft_reg | slot_begin | slot_end |
> booking_id | booking_priority | owner_uid
>
> --------------+------------------------+------------------------+------------+------------------+-----------
> EI-MCG | 2026-08-22 09:00:00+01 | 2026-08-22 10:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 10:00:00+01 | 2026-08-22 11:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 11:00:00+01 | 2026-08-22 12:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 12:00:00+01 | 2026-08-22 13:00:00+01 |
> 217 | 1 | jbloggs
> EI-MCG | 2026-08-22 12:00:00+01 | 2026-08-22 13:00:00+01 |
> 361 | 2 | rod
> EI-MCG | 2026-08-22 13:00:00+01 | 2026-08-22 14:00:00+01 |
> 217 | 1 | jbloggs
> EI-MCG | 2026-08-22 13:00:00+01 | 2026-08-22 14:00:00+01 |
> 361 | 2 | rod
> EI-MCG | 2026-08-22 14:00:00+01 | 2026-08-22 15:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 15:00:00+01 | 2026-08-22 16:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 16:00:00+01 | 2026-08-22 17:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 17:00:00+01 | 2026-08-22 18:00:00+01 |
> 361 | 1 | rod
> EI-MCG | 2026-08-22 18:00:00+01 | 2026-08-22 19:00:00+01 |
> 361 | 1 | rod
>
> So the key function for this work would be lag - it's a window function
which allows you to "look back" x rows (default 1) and see its data.
So lets start with a cte that tags new "groups"
with ordered as (SELECT *, lag(slot_end) over w,
CASE
WHEN slot_begin = lag(slot_end) OVER w
THEN 0
ELSE 1
END AS new_group
FROM slots
WINDOW w AS (
PARTITION BY aircraft_reg, booking_id, booking_priority, owner_uid
ORDER BY slot_begin
)
) select * from ordered
This will return all your rows with an additional column which indicates
whether or not this is a new group. So you use window to create groups of
records based on your 4 unique fields - order by slot_begin - then it walks
the rows and decides if the end time above it matches the start time and if
so it's not a new group. Obviously the first row for any set is a new group
Next up is placing each row in its group
so we start with our ordered cte above
, grouped AS (
SELECT *,
sum(new_group) OVER (
PARTITION BY aircraft_reg, booking_id, booking_priority,
owner_uid
ORDER BY slot_begin
) AS grp
FROM ordered
) select * from grouped
This is sort of a trick but it's simple enough - create a window again
against all our unique fields and then the sum function will sum the
new_group field for any record up until the row in question. So every time
a new group was tagged the sum will go 1 higher for each subsequent row
until the next group is found and then it will increase again and so on and
so forth. Hard for me to explain - but the select * will show it nicely as
the last field.
Finally
SELECT
aircraft_reg,
min(slot_begin) AS slot_begin,
max(slot_end) AS slot_end,
booking_id,
booking_priority,
owner_uid
FROM grouped
GROUP BY
aircraft_reg,
booking_id,
booking_priority,
owner_uid,
grp
ORDER BY slot_begin;
Simply pull your min and max for the begin and end based on your grp column.
Window functions are a very different beast but boy do they help when they
do!
Hope this moves you along.
John W Higgins
>
