Build departement query (1 Viewer)

Status
Not open for further replies.

namliam

The Mailman - AWF VIP
Local time
Today, 05:18
Joined
Aug 11, 2003
Messages
11,680
So i have a table

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.

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/
 
Last edited:

bastanu

Registered User.
Local time
Yesterday, 20:18
Joined
Apr 13, 2010
Messages
411
Have a look at this approach, probably could be improved, but it does what you wanted.

Cheers,
Vlad
 

Attachments

  • Database1.zip
    20.5 KB · Views: 10
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom