Grouping Programms until another Programm starts

[CNC-Reporter]

I hope the Title is somewhat correct, I have searched the Forums and Google, but didnt really know what the correct terms are for what i want to achieve.

I have set up a report database for our CNC-Machine monitoring tool, these machines run lengthy programms with a lot of stops in between for manual Inspection or other technological breaks which are unavoidable, therefore the same Programm can have widely different run times based on the machinist, logistical situation and a lot of adjacent factors between parts.

The data i recieve looks something like this:

program	id	program_start	program_end	program_state
PGM1	1	26.09.2023 04:31	26.09.2023 09:43	COMPLETED
PGM2	2	26.09.2023 07:51	26.09.2023 07:57	COMPLETED
PGM2	3	26.09.2023 10:08	26.09.2023 11:07	COMPLETED
PGM1	4	26.09.2023 10:08	26.09.2023 11:07	COMPLETED
PGM2	5	26.09.2023 11:07	26.09.2023 11:22	COMPLETED
PGM3	6	26.09.2023 11:07	26.09.2023 16:09	COMPLETED
PGM2	7	26.09.2023 14:17	26.09.2023 14:25	COMPLETED
PGM2	8	26.09.2023 16:09	26.09.2023 17:31	COMPLETED
PGM3	9	26.09.2023 16:09	26.09.2023 22:23	COMPLETED
PGM2	10	26.09.2023 20:50	26.09.2023 20:56	COMPLETED
PGM2	11	26.09.2023 22:24	26.09.2023 22:39	CANCELED_BY_USER
PGM3	12	26.09.2023 22:24	26.09.2023 22:39	CANCELED_BY_USER
PGM2	13	26.09.2023 22:39	26.09.2023 23:08	COMPLETED
PGM3	14	26.09.2023 22:39	26.09.2023 23:08	COMPLETED
PGM2	15	26.09.2023 23:08	26.09.2023 23:10	COMPLETED
PGM1	16	26.09.2023 23:08	26.09.2023 23:24	CANCELED_BY_USER
PGM2	17	26.09.2023 23:10	26.09.2023 23:24	COMPLETED
PGM1	18	26.09.2023 23:25	27.09.2023 05:25	COMPLETED
PGM2	19	27.09.2023 03:31	27.09.2023 03:38	COMPLETED
PGM2	20	27.09.2023 05:31	27.09.2023 05:53	CANCELED_BY_USER
PGM1	21	27.09.2023 05:31	27.09.2023 05:53	CANCELED_BY_USER
PGM2	22	27.09.2023 07:12	27.09.2023 08:48	COMPLETED
PGM1	23	27.09.2023 07:12	27.09.2023 08:48	COMPLETED
PGM4	24	27.09.2023 08:48	27.09.2023 10:21	CANCELED_BY_USER
PGM4	25	27.09.2023 10:23	27.09.2023 10:23	CANCELED_BY_USER
PGM4	26	27.09.2023 10:23	27.09.2023 10:46	CANCELED_BY_USER
PGM4	27	27.09.2023 10:46	27.09.2023 10:47	CANCELED_BY_USER
PGM4	28	27.09.2023 10:47	27.09.2023 12:46	CANCELED_BY_USER
PGM2	29	27.09.2023 12:33	27.09.2023 12:39	COMPLETED
PGM4	30	27.09.2023 12:47	27.09.2023 12:51	CANCELED_BY_USER
PGM4	31	27.09.2023 12:52	27.09.2023 13:01	CANCELED_BY_USER
PGM4	32	27.09.2023 13:01	27.09.2023 14:01	CANCELED_BY_USER
PGM4	33	27.09.2023 14:02	27.09.2023 16:31	CANCELED_BY_USER
PGM4	34	27.09.2023 16:33	27.09.2023 20:06	COMPLETED
PGM2	35	27.09.2023 20:18	27.09.2023 21:38	COMPLETED
PGM4	36	27.09.2023 20:18	28.09.2023 01:44	CANCELED_BY_USER
PGM4	37	28.09.2023 01:44	28.09.2023 01:53	CANCELED_BY_USER
PGM4	38	28.09.2023 01:53	28.09.2023 02:17	CANCELED_BY_USER
PGM4	39	28.09.2023 02:17	28.09.2023 04:30	CANCELED_BY_USER
PGM4	40	28.09.2023 04:31	28.09.2023 05:40	CANCELED_BY_USER
PGM4	41	28.09.2023 05:41	28.09.2023 05:58	COMPLETED
PGM2	42	28.09.2023 06:28		RUNNING
PGM4	43	28.09.2023 06:28		RUNNING

What i need is to group these uncompleted programms together to get some resemblence of one coherent programm, which is somewhat beyond my capabilities.

Ideally it would look something like this:

program	id	program_start	program_end	program_state
PGM1	1	26.09.2023 04:31	26.09.2023 09:43	COMPLETED
PGM2	2	26.09.2023 07:51	26.09.2023 11:07	COMPLETED
PGM1	4	26.09.2023 10:08	26.09.2023 11:07	COMPLETED
PGM2	5	26.09.2023 11:07	26.09.2023 11:22	COMPLETED
PGM3	6	26.09.2023 11:07	26.09.2023 16:09	COMPLETED
PGM2	7	26.09.2023 14:17	26.09.2023 17:31	COMPLETED
PGM3	9	26.09.2023 16:09	26.09.2023 22:23	COMPLETED
PGM2	10	26.09.2023 20:50	26.09.2023 22:39	COMPLETED
PGM3	12	26.09.2023 22:24	26.09.2023 22:39	CANCELED_BY_USER
PGM2	13	26.09.2023 22:39	26.09.2023 23:08	COMPLETED
PGM3	14	26.09.2023 22:39	26.09.2023 23:08	COMPLETED
PGM2	15	26.09.2023 23:08	26.09.2023 23:10	COMPLETED
PGM1	16	26.09.2023 23:08	26.09.2023 23:24	CANCELED_BY_USER
PGM2	17	26.09.2023 23:10	26.09.2023 23:24	COMPLETED
PGM1	18	26.09.2023 23:25	27.09.2023 05:25	COMPLETED
PGM2	19	27.09.2023 03:31	27.09.2023 05:53	COMPLETED
PGM2	20	27.09.2023 05:31	27.09.2023 05:53	CANCELED_BY_USER
PGM1	21	27.09.2023 05:31	27.09.2023 05:53	CANCELED_BY_USER
PGM2	22	27.09.2023 07:12	27.09.2023 08:48	COMPLETED
PGM1	23	27.09.2023 07:12	27.09.2023 08:48	COMPLETED
PGM4	24	27.09.2023 08:48	27.09.2023 12:46	COMPLETED
PGM2	29	27.09.2023 12:33	27.09.2023 12:39	COMPLETED
PGM4	30	27.09.2023 12:47	27.09.2023 20:06	COMPLETED
PGM2	35	27.09.2023 20:18	27.09.2023 21:38	COMPLETED
PGM4	36	27.09.2023 20:18	28.09.2023 05:58	COMPLETED

The red ones are grouped, so that the end time comes from the last occurence of said programm which are in one "block" (rows right below each other).

the program_state sadly is just an indicator for the real status, which means even if it states "CANCELED_BY_USER" it may have been completed, just the last NC-Step was not completely run to its end. In the same Fashion it is possible that the "COMPLETED" status may not entirely be true, if the machinist skipped some part of the code for some reasen, finished the programm and ran the skipped part afterwards, e.g. some tool was unavailable and needed to be changed, which may take some time.

The "id" column is actually some lengthy Hashcode i think, but i can obviously get this result in PowerQuery.

I could do this with Excel and VBA, but i would like to keep everything in database form for cleaner and easier access.

I am sorry if my grammar or wording seems weird, i am a native german speaker but try my best.

[Ashish_Mathur]

Hi,

I understand the reason why the rows of ID3 and ID2 have been subsumed into one (same program_end date and time).  But why (on what logic) have rows 7 and 8 (and other rows) been subsumed into 1.

Even when solving this in MS Excel, what would the Excel formula logic have been?

[CNC-Reporter]

The rows 7 and 8 fullfill my criteria for subsuming by:
1. being the same PGM
2. occuring right after each other

The same goes for id 24 to 28. Its all the same PGM and without being interrupted by anything else i would like them Grouped so they appear as one.

I hope this helps you help me.

You could say they were done together.

[lbendlin]

Very confusing.  Let's see if a graphical representation can help me understand what you need?

[CNC-Reporter]

That's an error on my part.

PGM2 can be ignored in the real Report, because its a SubProgram. The Monitor lists both and so it appears as if multiple programs are running at the same time.