Click here to Skip to main content
15,997,856 members
Search within:


How to get time duration of start and stop event in SQL
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL-Server
HI,

I have a database table which is having column -
[EventTimeStamp],                [Message],                   [Active]
2019-04-10 10:05:36.0374141	FLAVOR DRUM 2 TRIP ALARM	0
2019-04-10 10:05:38.0425288	FLAVOR SCREW 3 TRIP ALARM	0
2019-04-10 10:08:24.4010440	FRYER SUBMERG TRIP ALARM	1
2019-04-10 10:08:26.2111475	FRYER TAKEOUT TRIP ALARM	1
2019-04-10 10:09:02.2432084	EMERGENCY STOP ALARM	        1
2019-04-10 10:09:02.2432084	SCREW CONV TRIP ALARM	        1
2019-04-10 10:09:04.2133211	CIRCULATION PUMP TRIP ALARM	1
2019-04-10 10:09:04.2133211	HOODLIFTING TRIP ALARM	        1
2019-04-10 10:09:04.2133211	VOLTAGE UNBALANCE ALARM	        1
2019-04-10 10:09:06.2164357	CIRCULATION PUMP TRIP ALARM	0
2019-04-10 10:09:57.5903741	CROSS CONV TRIP ALARM	        0
2019-04-10 10:09:57.5903741	DEOILING CONV TRIP ALARM	0
2019-04-10 10:09:57.5903741	FILTER DRUM TRIP ALARM	        0
2019-04-10 10:09:57.5913742	FILTER REEL TRIP ALARM	        0  
2019-04-10 10:09:57.5913742	FLAVOR AUGER 1 TRIP ALARM	0
2019-04-10 10:09:57.5913742	FLAVOR AUGER 2 TRIP ALARM	0
2019-04-10 10:09:57.5913742	FLAVOR AUGER 3 TRIP ALARM	0
2019-04-10 10:09:57.5923742	FLAVOR DRUM 2 TRIP ALARM	0
2019-04-10 10:09:57.5923742	FLAVOR DRUM 4 TRIP ALARM	0
2019-04-10 10:09:57.5923742	FLAVOR SCREW 1 TRIP ALARM	0
2019-04-10 10:09:57.5933743	FLAVOR SCREW 2 TRIP ALARM	0
2019-04-10 10:09:57.5933743	FLAVOR SCREW 3 TRIP ALARM	0
2019-04-10 10:09:57.5933743	FLAVOR SCREW 4 TRIP ALARM	0
2019-04-10 10:09:57.5933743	SHAKER TRIP ALARM	        0
2019-04-10 10:10:08.2569842	BLOWER TRIP ALARM	        0
2019-04-10 10:10:08.2569842	CIRCULATION PUMP TRIP ALARM	0
2019-04-10 10:10:08.2569842	EMERGENCY STOP ALARM	        1
2019-04-10 10:10:08.2569842	FRYER SUBMERG TRIP ALARM	1
2019-04-10 10:10:08.2579843	FRYER TAKEOUT TRIP ALARM	1
2019-04-10 10:10:08.2579843	HOODLIFTING TRIP ALARM	        1
2019-04-10 10:10:08.2579843	PART CONVEYOR 1 TRIP ALARM	0
2019-04-10 10:10:08.2579843	PART CONVEYOR 2 TRIP ALARM	0
2019-04-10 10:10:08.2579843	PART CONVEYOR 5 TRIP ALARM	0
2019-04-10 10:10:08.2579843	SCREW CONV TRIP ALARM	        1
2019-04-10 10:10:08.2579843	TRANSFER PUMP TRIP ALARM	0
2019-04-10 10:10:08.2589843	VIBRATOR 1 TRIP ALARM	        0
2019-04-10 10:10:08.2589843	VIBRATOR 2 TRIP ALARM	        0
2019-04-10 10:10:08.2589843	VOLTAGE UNBALANCE ALARM	        1
2019-04-10 10:10:11.2121532	PART CONVEYOR 3 TRIP ALARM	0
2019-04-10 10:10:11.2121532	PART CONVEYOR 4 TRIP ALARM	0
2019-04-10 10:10:11.2131533	PART CONVEYOR 6 TRIP ALARM	0
2019-04-10 10:10:11.2131533	REVERSIBLE FEED CONV 1 REV TRIP ALARM	0
2019-04-10 10:10:11.2141533	REVERSIBLE FEED CONV 2 REV TRIP ALARM	0
2019-04-10 10:10:11.2141533	REVERSIBLE FEED CONV 3 REV TRIP ALARM	0
2019-04-10 10:10:11.2141533	REVERSIBLE FEED CONV 4 REV TRIP ALARM	0
2019-04-10 10:10:11.2151534	SPARE FEEDER TRIP ALARM	        0
2019-04-10 10:10:15.2963868	HOODLIFTING DOWN LS 2 ALARM	0
2019-04-10 10:10:15.2963868	HOODLIFTING UP LS 1 ALARM	0
2019-04-10 10:10:15.2963868	OIL LEVEL LOW ALARM	        0
2019-04-10 10:10:15.2973869	REVERSIBLE FEED CONV 5 REV TRIP ALARM	0
2019-04-10 10:10:15.2973869	REVERSIBLE FEED CONV 6 REV TRIP ALARM	0
2019-04-10 10:10:15.2973869	REVERSIBLE FEED CONV 7 REV TRIP ALARM	0
2019-04-10 10:10:15.2973869	REVERSIBLE FEED CONV 8 REV TRIP ALARM	0
2019-04-10 10:10:15.2973869	REVERSIBLE FEED CONV 9 REV TRIP ALARM	0
2019-04-10 10:10:32.1793525	ELEVATOR TRIP ALARM	        0
2019-04-10 10:10:32.1793525	FLAVOR AUGER 4 TRIP ALARM	0
2019-04-10 10:10:32.1793525	FLAVOR DRUM 1 TRIP ALARM	0
2019-04-10 10:10:32.1813526	FLAVOR DRUM 3 TRIP ALARM	0
2019-04-10 10:10:32.1813526	FRYER INLET TEMP LOW ALARM	0
2019-04-10 10:10:32.1813526	REVERSIBLE FEED CONV 10 REV TRIP ALARM	0


Where event time is time, message is event and 0 and 1 is showing message on or off conditions

i have to make this table as below format

MESSAGE      EVENT IN TIME                  EVENT OUT TIME       DURATION



Please help to resolve this

What I have tried:

i have made two individual tables for both INTIME event and OUTTIME event, and join to both with message column. but where events on is available and outtime is not available it is repeating the value.
Posted
Updated 3-May-19 11:24am
v2
Add a Solution
Comments
CHill60 3-May-19 8:50am    
Don't have two tables, it doesn't help. Firstly, how are records on your table generated? Is it "polling" i.e. the current state of the alarm is checked on a regular interval basis. Or is it "triggered" i.e. a record is supposed to be written whenever the active status changes?
The table you have presented as an example does not fit either of those descriptions and appears to be quite random.
Also - can we make any assumptions for data outside of the range - e.g. can we assume that the initial state of any alarm is "off" or should it be the opposite of the first recorded state?
Once we have established how the table is populated we can advise on techniques for determining duration.
[no name] 3-May-19 9:22am    
WHAT do you WANT to happen when there is no OUTTIME for an INTIME?

1 solution

Do you want the next time to be the out time?

https://blog.sqlauthority.com/2013/09/22/sql-server-how-to-access-the-previous-row-and-next-row-value-in-select-statement/[^]

If you order by time, you can query values from the next row
 
Share this answer
 
Posted
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
Summarize start and end durations by 30 minute interval
I need to design a query to get data between 2 time duration
Start SQL service if it stopped
start and end time of a schedule using duration in sql Server
Storyboard time/duration issue
calculating time duration
How do I stop this pop up event from populating he jcombobox each time it is clicked?
Clock/Stop watch implementation to calculate elapsed time between two events.
How to get the time duration in C# web app
Design a music player that randomly pauses and skips songs to achieve a uniform overall play time, while respecting fixed play durations. Entire collection of songs for a specified time.

Advertise
Privacy
Cookies
Terms of Use
Last Updated 3 May 2019
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web03 2.8:2024-07-22:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900