Forum

Can someone help me...
 
Notifications
Clear all

Can someone help me to write a SQL query to generate output as shown below?


KV Kumar
Posts: 27
Admin
Topic starter
(@vinodkrsetty)
Member
Joined: 2 years ago

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

1 Reply
Info User
Posts: 36
 Info User
(@Info User)
Joined: 1 year ago

Try the below query

Select site+' '+ 'Start'+cast(Timestamp, datetime()) As Starttime, site+' '+ 'Stop'+cast(Timestamp, datetime()) As EndTime, unit

From input
Groupby starttime.

Reply
Share: