Sql query to create graph from a table of events

Question

I have a plain old table in SQL Server like so:

JOB    Step     Timestamp
----------------------------------
1      begin    12/25/2021 1:00 AM
1      foo      12/25/2021 1:01 AM
1      bar      12/25/2021 1:02 AM
1      end      12/25/2021 1:03 AM

It is a list of steps, that transition from one to the other, and the transition is determined by the timestamp. I would like to render it as a graph of events, so am trying to query it with results like:

JOB    Source    Target   Timestamp
--------------------------------------------
1      begin     foo      12/25/2021 1:01 AM
1      foo       bar      12/25/2021 1:02 AM  
1      bar       end      12/25/2021 1:03 AM

This is not a SQL Server graph table but I'd like it to behave like one in this case.

This is ultimately going to be rendered in PowerBI using a force directed graph visualization, so answers in T-SQL or DAX would work for my use case.

Just guessing, but the downvote may be because the question included no attempt to solve the problem. — Dan Guzman, Dec 25 '21 at 22:36
@DanGuzman - could be. unfortunately all of my attempts were disasters. windowing function always get the best of me. — dkackman, Dec 25 '21 at 23:55

score 3 · Accepted Answer · answered Dec 25 '21 at 21:37

In t-sql this is simple with lead() window function, the following should produce your expected results

select JOB, [Source], [Target], [TimeStamp]
from (
    select JOB, Step [Source],
        Lead(Step) over(partition by JOB order by [Timestamp]) [Target],
        Lead([Timestamp]) over(partition by JOB order by [Timestamp]) [TimeStamp]
    from t
)t
where [Target] is not null;

score 2 · Answer 2 · answered Dec 25 '21 at 23:58

With DAX

You can write two measures like this to achieve the end goal

Source =
VAR _time1 =
    MAX ( 'Table'[Timestamp] )
VAR _beginTime =
    CALCULATE (
        MAX ( 'Table'[Timestamp] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[JOB] = MAX ( 'Table'[JOB] )
                && 'Table'[Timestamp] < _time1
        )
    )
VAR _begin =
    CALCULATE (
        MAX ( 'Table'[Step] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[JOB] = MAX ( 'Table'[JOB] )
                && 'Table'[Timestamp] = _beginTime
        )
    )
RETURN
    _begin

    

Target =
IF ( [Source] <> BLANK (), MAX ( 'Table'[Step] ) )

Sql query to create graph from a table of events

2 Answers2