I have this table
Id_usuario | startime | endtime
------------+-----------------------+--------------------
123124 | 2023-03-01 08:04:05 | 2023-03-01 08:04:06
123124 | 2023-03-01 08:04:06 | 2023-03-01 18:04:37
123124 | 2023-03-01 18:04:37 | 2023-03-01 18:04:38
567123 | 2023-03-01 07:20:33 | 2023-03-01 07:20:34
567123 | 2023-03-01 07:20:34 | 2023-03-01 23:59:58
567123 | 2023-03-01 23:59:58 | 2023-03-01 23:59:59
567123 | 2023-03-02 00:00:00 | 2023-03-02 00:00:01
567123 | 2023-03-02 00:00:01 | 2023-03-02 00:41:33
567123 | 2023-03-02 00:41:33 | 2023-03-02 00:41:34
567123 | 2023-03-02 08:02:47 | 2023-03-02 08:02:48
567123 | 2023-03-02 08:02:48 | 2023-03-01 18:42:35
567123 | 2023-03-02 18:42:36 | 2023-03-01 18:42:36
and I need to know start time and the end time.
I have tried this:
select
id,
cast ([Startime]as date) as Fecha,
min (cast ([Startime] as datetime)) as inicio,
max (cast ([endtime] as datetime)) as final,
from
tabla
group by
id, cast ([Startime]as date)
And it works for all the personnel who's shift star and ends on the same day however for those with night shift it won't or for those who for some reason stay connected to the app after midnight neither.
Results:
Id_usuario | starttime | end time
------------+-----------------------+--------------------
123124 | 2023-03-01 08:04:05 | 2023-03-01 18:04:38
567123 | 2023-03-01 07:20:33 | 2023-03-01 23:59:59
567123 | 2023-03-02 00:00:00 | 2023-03-01 18:42:36
What I need :
Id_usuario | starttime | end time
------------+-----------------------+--------------------
123124 | 2023-03-01 08:04:05 | 2023-03-01 18:04:38
567123 | 2023-03-01 07:20:33 | 2023-03-02 00:41:34
567123 | 2023-03-02 08:02:47 | 2023-03-01 18:42:36
I'd be grateful if anyone could help me
