Forum

Can someone help me...
 
Share:
Notifications
Clear all

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

2 Posts
2 Users
0 Likes
302 Views
KV Kumar
Posts: 27
Admin
Topic starter
(@vinodkrsetty)
Eminent Member
Joined: 3 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
Guest
(@Info User)
Eminent Member
Joined: 2 years 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

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Share: