Input
Site Process Timestamp Units
A1 Start 2020-06-01 9:00 1
A1 Load 2020-06-01 9:01 1
A1 Load 2020-06-01 9:02 1
A1 Stop 2020-06-01 9:03 0
A2 Start 2020-06-01 9:00 1
A2 Load 2020-06-01 9:01 1
A2 Load 2020-06-01 9:02 1
A2 Stop 2020-06-01 9:03 0
A1 Start 2020-06-01 9:04 1
A1 Load 2020-06-01 9:05 1
A1 Load 2020-06-01 9:06 1
A1 Stop 2020-06-01 9:06 0
Output
Site StartTime EndTime Loop Units
A1 2020-06-01 9:00 2020-06-01 9:03 1 3
A2 2020-06-01 9:00 2020-06-01 9:03 1 3
A1 2020-06-01 9:04 2020-06-01 9:06 2 3
Try the below query
Select site+' '+ 'Start'+cast(Timestamp, datetime()) As Starttime, site+' '+ 'Stop'+cast(Timestamp, datetime()) As EndTime, unit
From input
Groupby starttime.
Leave a reply
Latest Post: Query Optimization: A Comprehensive Guide for Stored Procedures Our newest member: anjalirrb Recent Posts Unread Posts
