So i have a table
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:
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.
Cross posted to the access forum since it gets more readers and the problem seems universal enough for me to transpose the solution.
Cross post is here: https://www.access-programmers.co.uk/forums/threads/build-departement-query.310926/
However this problem is residing in a SQL server database, so I hope maybe there is a specific SQL server solution to this?
Anyone have a good solution to (dynamicaly) do this?
Code:
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:
Code:
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.
Cross posted to the access forum since it gets more readers and the problem seems universal enough for me to transpose the solution.
Cross post is here: https://www.access-programmers.co.uk/forums/threads/build-departement-query.310926/
However this problem is residing in a SQL server database, so I hope maybe there is a specific SQL server solution to this?
Anyone have a good solution to (dynamicaly) do this?