By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Run (
RunID VARCHAR(10),
Timestamp DATETIME2(0)
)
INSERT INTO Run
VALUES
('XYZ123', '2024-12-19 15:00:55'),
('XYZ980', '2024-12-19 14:53:35'),
('XYZ456', '2024-12-19 14:53:03'),
('ZZZ111', '2024-12-20 01:02:03'),
('ZZZ222', '2024-12-20 11:12:13')
CREATE TABLE TagHistory (
Timestamp DATETIME2(0),
Tagname VARCHAR(10),
Value INT
)
INSERT INTO TagHistory
VALUES
('2024-12-19 15:00:57', 'RUNNING', 0),
('2024-12-19 15:00:55', 'RUNNING', 1),
('2024-12-19 14:53:38', 'RUNNING', 0),
('2024-12-19 14:53:35', 'RUNNING', 1),
('2024-12-19 14:53:08', 'RUNNING', 0),
('2024-12-19 14:53:03', 'RUNNING', 1),
-- more data
('2024-12-20 01:02:05', 'RUNNING', 0), -- Not exactly equal to Run.Timestamp
('2024-12-20 01:02:04', 'RUNNING', 1),
-- more data
('2024-12-20 11:22:14', 'RUNNING', 1) -- Start, but no end
14 rows affected
SELECT R.RunID, S.Timestamp AS StartTime, E.Timestamp AS EndTime
FROM Run R
JOIN TagHistory S
ON S.timestamp = R.Timestamp
AND S.Tagname = 'RUNNING'
AND S.Value = 1
CROSS APPLY (
SELECT TOP 1 *
FROM TagHistory E
WHERE E.timestamp >= S.Timestamp
AND E.Tagname = 'RUNNING'
AND E.Value = 0
ORDER BY E.Timestamp
) E
ORDER BY R.Timestamp
| RunID | StartTime | EndTime |
|---|---|---|
| XYZ456 | 2024-12-19 14:53:03 | 2024-12-19 14:53:08 |
| XYZ980 | 2024-12-19 14:53:35 | 2024-12-19 14:53:38 |
| XYZ123 | 2024-12-19 15:00:55 | 2024-12-19 15:00:57 |
SELECT R.RunID, R.Timestamp AS [StartTime(RunTime)], E.Timestamp AS EndTime
FROM Run R
CROSS APPLY (
SELECT TOP 1 *
FROM TagHistory E
WHERE E.timestamp >= R.Timestamp
AND E.Tagname = 'RUNNING'
AND E.Value = 0
ORDER BY E.Timestamp
) E
ORDER BY R.Timestamp
| RunID | StartTime(RunTime) | EndTime |
|---|---|---|
| XYZ456 | 2024-12-19 14:53:03 | 2024-12-19 14:53:08 |
| XYZ980 | 2024-12-19 14:53:35 | 2024-12-19 14:53:38 |
| XYZ123 | 2024-12-19 15:00:55 | 2024-12-19 15:00:57 |
| ZZZ111 | 2024-12-20 01:02:03 | 2024-12-20 01:02:05 |
SELECT
R.RunID, R.Timestamp as RunIdTime,
S.Timestamp AS StartTime, E.Timestamp AS EndTime
FROM Run R
OUTER APPLY (
SELECT TOP 1 *
FROM TagHistory S
WHERE S.timestamp >= R.Timestamp
AND S.Tagname = 'RUNNING'
AND S.Value = 1
ORDER BY S.Timestamp
) S
OUTER APPLY (
SELECT TOP 1 *
FROM TagHistory E
WHERE E.timestamp >= S.Timestamp
AND E.Tagname = 'RUNNING'
AND E.Value = 0
ORDER BY E.Timestamp
) E
ORDER BY R.Timestamp
| RunID | RunIdTime | StartTime | EndTime |
|---|---|---|---|
| XYZ456 | 2024-12-19 14:53:03 | 2024-12-19 14:53:03 | 2024-12-19 14:53:08 |
| XYZ980 | 2024-12-19 14:53:35 | 2024-12-19 14:53:35 | 2024-12-19 14:53:38 |
| XYZ123 | 2024-12-19 15:00:55 | 2024-12-19 15:00:55 | 2024-12-19 15:00:57 |
| ZZZ111 | 2024-12-20 01:02:03 | 2024-12-20 01:02:04 | 2024-12-20 01:02:05 |
| ZZZ222 | 2024-12-20 11:12:13 | 2024-12-20 11:22:14 | null |