I want to ask, how to make output below :
Date NIP Clock_in Clock_out 24/03/2020 15017 8:00:02 18:27:12 24/03/2020 H8137 8:08:01 14:22:50
from data like this below :
NIP TimeStamps 15017 2020-03-24 08:00:02.550 15017 2020-03-24 08:01:00.117 15017 2020-03-24 08:05:30.440 15017 2020-03-24 08:05:37.397 15017 2020-03-24 08:06:25.160 H8137 2020-03-24 08:08:01.537 15017 2020-03-24 10:41:15.500 H8137 2020-03-24 11:40:37.327 H8137 2020-03-24 11:41:43.750 H8137 2020-03-24 13:25:01.000 H8137 2020-03-24 14:11:26.000 H8137 2020-03-24 14:11:39.000 H8137 2020-03-24 14:22:50.000 15017 2020-03-24 18:26:10.000 15017 2020-03-24 18:26:16.000 15017 2020-03-24 18:26:46.000 15017 2020-03-24 18:26:51.000 15017 2020-03-24 18:27:06.000 15017 2020-03-24 18:27:12.000
with rules the ‘Clock_in’ is the earliest and ‘Clock_out’ is the latest one of the ‘Timestamps’ group by ‘NIP’
Advertisement
Answer
If you are using SQL server you can make sure of Cast and group by.
with cte as ( select '15017' as NIP, '2020-03-24 08:00:02.550' as TimeStamps union all select '15017' as NIP, '2020-03-24 08:01:00.117' as TimeStamps union all select '15017' as NIP, '2020-03-24 08:05:30.440' as TimeStamps union all select '15017' as NIP, '2020-03-24 08:05:37.397' as TimeStamps union all select '15017' as NIP, '2020-03-24 08:06:25.160' as TimeStamps union all select 'H8137' as NIP, '2020-03-24 08:08:01.537' as TimeStamps union all select '15017' as NIP, '2020-03-24 10:41:15.500' as TimeStamps union all select 'H8137' as NIP, '2020-03-24 11:40:37.327' as TimeStamps union all select 'H8137' as NIP, '2020-03-24 11:41:43.750' as TimeStamps union all select 'H8137' as NIP, '2020-03-24 13:25:01.000' as TimeStamps union all select 'H8137' as NIP, '2020-03-24 14:11:26.000' as TimeStamps union all select 'H8137' as NIP, '2020-03-24 14:11:39.000' as TimeStamps union all select 'H8137' as NIP, '2020-03-24 14:22:50.000' as TimeStamps union all select '15017' as NIP, '2020-03-24 18:26:10.000' as TimeStamps union all select '15017' as NIP, '2020-03-24 18:26:16.000' as TimeStamps union all select '15017' as NIP, '2020-03-24 18:26:46.000' as TimeStamps union all select '15017' as NIP, '2020-03-24 18:26:51.000' as TimeStamps union all select '15017' as NIP, '2020-03-24 18:27:06.000' as TimeStamps union all select '15017' as NIP, '2020-03-24 18:27:12.000' as TimeStamps ) select cast(TimeStamps as date) Date , NIP, min(cast (TimeStamps as time)) Clock_in, max(cast (TimeStamps as time)) Clock_out from cte group by cast(TimeStamps as date) , NIP;
output:
