CODEWITHSUNDEEP
sqlsql-servertranspose
NEUser
NEUser

Reputation: 89

SQL: Data Transpose

I have a SQL script that outputs this:

timestamp tagname value
12/19/2024 15:00:57 RUNNING 0
12/19/2024 15:00:55 RUNNING 1
12/19/2024 14:53:38 RUNNING 0
12/19/2024 14:53:35 RUNNING 1
12/19/2024 14:53:08 RUNNING 0
12/19/2024 14:53:03 RUNNING 1

The script needs to take this data, evaluate when the value is 1 then capture the timestamp as StartTime and when the value is 0 capture the timestamp as EndTime. Further I need to query a table that has RunID associated with the StartTime and use that for the RunID. The RunID is set by a user unique (assume that the RunID is unique) , its set when the Running tag goes to 1 in the machine.

RunID Timestamp
XYZ123 12/19/2024 15:00:55
XYZ980 12/19/2024 14:53:35
XYZ456 12/19/2024 14:53:03

The final output should be:

RunID StartTime Endtime
XYZ456 12/19/2024 14:53:03 12/19/2024 14:53:08
XYZ980 12/19/2024 14:53:35 12/19/2024 14:53:38
XYZ123 12/19/2024 15:00:55 12/19/2024 15:00:57

Upvotes: 0

Views: 92

Answers (1)

T N
T N

Reputation: 9907

Assuming that the start timestamp is always identical to the run timestamp, matching the run to the start is a simple join. To find the EndTime, you can use the CROSS APPLY(SELECT TOP 1 ... ORDER BY ...) pattern to locate the next end time following the start.

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

Since you don't really pull any other data from the start time row, that join can actually be eliminated.

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

However, if there is a chance that the start time can be slightly later than the run timestamp (possible with two separate inserts each with its own GETDATE() reference), it would be more reliable to use the CROSS APPLY technique to retrieve both the start and end timestamp rows.

Also, if there is a chance that one or both history rows might be missing (perhaps a job is still running), changing the CROSS APPLY to an OUTER APPLY will still yield a result rows (similar to a LEFT JOIN.

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

The above queries assume that the timestamp values do not have ambiguities, such as duplicate run timestamps, overlapping runs, or one run's end timestamp equaling the next run's start time.

Sample results (with some extra data):

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

See this db<>fiddle for a demo.

Upvotes: 2

Related Questions