Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Calculation of a status life duration
-- Create sample CREATE TABLE t_statusHistory ( id int IDENTITY(1,1) PRIMARY KEY, message int NOT NULL, status_code int NOT NULL, stamp datetime DEFAULT GETDATE() ); INSERT INTO t_statusHistory (message, status_code) VALUES (1, 0), (2, 0), (3, 0), (4, 0), (5, 0); WAITFOR DELAY '00:00:01'; INSERT INTO t_statusHistory (message, status_code) VALUES (1, 1), (2, 1); WAITFOR DELAY '00:00:01'; INSERT INTO t_statusHistory (message, status_code) VALUES (2, 3), (3, 3), (5, 3); WAITFOR DELAY '00:00:01'; INSERT INTO t_statusHistory (message, status_code) VALUES (1, 4), (2, 4), (3, 4), (5, 3); -- Show status history SELECT * FROM t_statusHistory; -- Show durations SELECT previous.message, previous.status_code, DATEDIFF(second, previous.stamp, ISNULL(MIN(next.stamp), CURRENT_TIMESTAMP)) duration FROM t_statusHistory previous LEFT JOIN t_statusHistory next ON previous.message=next.message AND previous.status_code <> next.status_code AND next.stamp > previous.stamp GROUP BY previous.message, previous.status_code, previous.stamp;
run
|
edit
|
history
|
help
0
Cinema latinoamericano
Teacher
a
Week 6 Data Base IET
Allocations Check
updated
Sql varchar to date
1
First Query
SQL_Joins_RankingFunctions