Time Periods every 15 minutes

[krmvacar]

Hi all,

I have two fields called work start and personnel number. What I want to do here is to find out how many people have started working in 15-minute periods. I would be very happy if you could help.

workstart	personelnum
03.01.2022 07:46:06	13021
04.01.2022 08:03:09	13021
05.01.2022 07:44:56	13021
06.01.2022 07:42:26	13021
06.01.2022 14:24:07	13021
07.01.2022 07:44:08	13021
08.01.2022 07:35:03	13021
10.01.2022 15:48:01	13021
11.01.2022 15:53:40	13021
12.01.2022 15:46:32	13021
13.01.2022 15:48:20	13021
14.01.2022 15:49:43	13021
15.01.2022 15:47:53	13021
17.01.2022 15:51:38	13021
18.01.2022 15:49:55	13021
19.01.2022 15:45:46	13021
20.01.2022 15:47:42	13021
21.01.2022 15:47:58	13021
22.01.2022 15:46:52	13021
24.01.2022 07:42:55	13021
25.01.2022 07:42:38	13021
26.01.2022 07:41:31	13021
27.01.2022 07:41:45	13021
28.01.2022 07:40:51	13021
29.01.2022 07:44:18	13021
31.01.2022 15:49:42	13021
02.01.2022 23:59:55	17051
03.01.2022 23:50:54	17051
04.01.2022 23:53:18	17051
05.01.2022 23:54:31	17051
06.01.2022 23:48:38	17051
07.01.2022 23:50:34	17051
10.01.2022 15:49:23	17051
11.01.2022 15:49:04	17051
12.01.2022 15:48:11	17051
13.01.2022 15:53:39	17051
14.01.2022 15:48:33	17051
15.01.2022 15:52:29	17051
17.01.2022 07:55:36	17051
18.01.2022 08:04:18	17051
19.01.2022 07:57:12	17051
20.01.2022 07:57:13	17051
21.01.2022 07:56:35	17051
22.01.2022 07:55:56	17051
23.01.2022 23:56:43	17051
24.01.2022 23:47:13	17051
25.01.2022 23:51:11	17051
26.01.2022 23:51:48	17051
27.01.2022 23:48:22	17051
28.01.2022 23:46:50	17051
31.01.2022 15:49:07	17051

I would like a result like this if possible.How many different employees entered the job on the same day and at what intervals.

Best Regards

	06:00-06:15	06:15-06:30	06:30-06:45	07:45-08:00	08:15-08:30
01.01.2022	0	0	0	1	0
02.01.2022	0	1	0	0	1

[MarcoWedel · Accepted solution]

maybe like this:

table1:
LOAD *,
     DayName(workstart)		as workstartDate,
     Time(Frac(workstart))	as workstartTime,
     Dual(Time(Floor(Frac(workstart),'00:15:00'),'hh:mm')&'-'&Time(Floor(Frac(workstart),'00:15:00')+'00:15:00','hh:mm'),Floor(Frac(workstart),'00:15:00'))	as workstartPeriod;
LOAD Timestamp#(workstart,'DD.MM.YYYY hh:mm:ss') as workstart, 
     personelnum
INLINE [
    workstart, personelnum
    03.01.2022 07:46:06, 13021
    04.01.2022 08:03:09, 13021
    05.01.2022 07:44:56, 13021
    06.01.2022 07:42:26, 13021
    06.01.2022 14:24:07, 13021
    07.01.2022 07:44:08, 13021
    08.01.2022 07:35:03, 13021
    10.01.2022 15:48:01, 13021
    11.01.2022 15:53:40, 13021
    12.01.2022 15:46:32, 13021
    13.01.2022 15:48:20, 13021
    14.01.2022 15:49:43, 13021
    15.01.2022 15:47:53, 13021
    17.01.2022 15:51:38, 13021
    18.01.2022 15:49:55, 13021
    19.01.2022 15:45:46, 13021
    20.01.2022 15:47:42, 13021
    21.01.2022 15:47:58, 13021
    22.01.2022 15:46:52, 13021
    24.01.2022 07:42:55, 13021
    25.01.2022 07:42:38, 13021
    26.01.2022 07:41:31, 13021
    27.01.2022 07:41:45, 13021
    28.01.2022 07:40:51, 13021
    29.01.2022 07:44:18, 13021
    31.01.2022 15:49:42, 13021
    02.01.2022 23:59:55, 17051
    03.01.2022 23:50:54, 17051
    04.01.2022 23:53:18, 17051
    05.01.2022 23:54:31, 17051
    06.01.2022 23:48:38, 17051
    07.01.2022 23:50:34, 17051
    10.01.2022 15:49:23, 17051
    11.01.2022 15:49:04, 17051
    12.01.2022 15:48:11, 17051
    13.01.2022 15:53:39, 17051
    14.01.2022 15:48:33, 17051
    15.01.2022 15:52:29, 17051
    17.01.2022 07:55:36, 17051
    18.01.2022 08:04:18, 17051
    19.01.2022 07:57:12, 17051
    20.01.2022 07:57:13, 17051
    21.01.2022 07:56:35, 17051
    22.01.2022 07:55:56, 17051
    23.01.2022 23:56:43, 17051
    24.01.2022 23:47:13, 17051
    25.01.2022 23:51:11, 17051
    26.01.2022 23:51:48, 17051
    27.01.2022 23:48:22, 17051
    28.01.2022 23:46:50, 17051
    31.01.2022 15:49:07, 17051
];

[krmvacar]

Hi @MarcoWedel ,

Thank you so much.İts working.

Best Regards

[krmvacar]

Hi @MarcoWedel 

Can I ask something in addition to this question?

If the working hours range of customer between 07:30-07:45 and 07:45-08:00, can we show it as 08:00-08:15?

for example;

my data is=

	07:30-07:45	07:45-08:00	08:00-08:15
01.01.2022	1	0	0
02.01.2022	0	1	0

ı want to result:

	07:30-07:45	07:45-08:00	08:00-08:15
01.01.2022	0	0	1
02.01.2022	0	0	1

thank you so much for helpıng