WITH RECURSIVE
Connections (ConnectionId, UserId, IPAddress, CommunicationLink,
Application, LoginTimestamp, ConnectionMinutes, LastTimestamp, IdleMinutes)
AS
(
( SELECT next_connection( NULL ) AS ConnectionId,
connection_property( 'Userid', ConnectionId) AS UserId,
connection_property( 'NodeAddress', ConnectionId) AS IPAddress,
connection_property( 'CommLink', ConnectionId) AS
CommunicationLink,
connection_property( 'AppInfo', ConnectionId) AS Application,
CAST(connection_property( 'LoginTime', ConnectionId) AS CHAR(19))
AS LoginTimestamp,
DATEDIFF(Minute, connection_property( 'LoginTime', ConnectionId),
Current Timestamp ) AS ConnectionMinutes,
CAST(connection_property( 'LastReqTime', ConnectionId) AS
CHAR(19)) AS LastTimestamp,
DATEDIFF(Minute, connection_property( 'LastReqTime',
ConnectionId), Current Timestamp ) AS IdleMinutes
FROM Dummy AS Seed
)
UNION ALL
( SELECT next_connection( Seed.ConnectionId ) AS ConnectionId,
connection_property( 'Userid', ConnectionId) AS UserId,
connection_property( 'NodeAddress', ConnectionId) AS IPAddress,
connection_property( 'CommLink', ConnectionId) AS
CommunicationLink,
connection_property( 'AppInfo', ConnectionId) AS Application,
CAST(connection_property( 'LoginTime', ConnectionId) AS CHAR(19))
AS LoginTimestamp,
DATEDIFF(Minute, connection_property( 'LoginTime', ConnectionId),
Current Timestamp ) AS ConnectionMinutes,
CAST(connection_property( 'LastReqTime', ConnectionId) AS
CHAR(19)) AS LastTimestamp,
DATEDIFF(Minute, connection_property( 'LastReqTime',
ConnectionId), Current Timestamp ) AS IdleMinutes
FROM Dummy,
Connections AS Seed
WHERE ConnectionId IS NOT NULL
)
)
SELECT * FROM Connections