Build departement query

[namliam]

So i have a table

ID    BATCH  DEPARTMENT   START       END
5     X      1            01-4-2020   02-4-2020
9     X      3            02-4-2020   05-4-2020
15    X      4            05-4-2020   07-4-2020
28    X      4            07-4-2020   11-4-2020
44    X      3            11-4-2020   15-4-2020
6     X      3            15-4-2020   18-4-2020
66    X      3            18-4-2020   22-4-2020
99    X      1            22-4-2020   25-4-2020

In actually I only have the start dates (and times) but I am able to transpose the end date(times) without much trouble.
What I am trying to reach is this:

BATCH  DEPARTMENT   START       END
X      1            01-4-2020   02-4-2020
X      3            02-4-2020   05-4-2020
X      4            05-4-2020   11-4-2020
X      3            11-4-2020   22-4-2020
X      1            22-4-2020   25-4-2020

Offcourse there are different batches and the number of lines per batch or per department are random as well as the order.
But I am trying to find the back to back time periods of the same period and make them as one.
Thus making from department 3, two records 2-4 thru 5-4 and 11-4 thru 22-4.

Anyone have a good solution to (dynamicaly) do this?

I am cross posting this to the SQL Server forum, as it is an actual SQL Server database.
Cross post is here: https://www.access-programmers.co.uk/forums/threads/build-departement-query.310927/

 

[bastanu]

Have a look at this approach, probably could be improved, but it does what you wanted.

Cheers,
Vlad

 

[Uncle Gizmo]

So i have a table

AND TWO THREADS!!!

I've locked this thread because there are two threads running asking/answering the same question so please post your responses in the other thread HERE:- https://www.access-programmers.co.uk/forums/threads/build-departement-query.310927/