DECLARE @eventID INT = {0} SELECT Event.ID AS [Events/Event/@id], Event.ID AS [Event/ID], EventType.Name AS [Event/Type], Event.StartTime AS [Event/StartTime], Event.EndTime AS [Event/EndTime], Location.LocationKey AS [Station/Key], Location.Name AS [Station/Name], Meter.AssetKey AS [Meter/Key], Meter.Name AS [Meter/Name], (SELECT COUNT(MeasurementTypeID) FROM Channel WHERE AssetID = (SELECT AssetID FROM Event WHERE ID = {0}) AND Trend = 0 AND MeasurementTypeID = (SELECT ID FROM MeasurementType WHERE Name = 'Voltage')) AS [Meter/VChannelCount], (SELECT COUNT(MeasurementTypeID) FROM Channel WHERE AssetID = (SELECT AssetID FROM Event WHERE ID = {0}) AND Trend = 0 AND MeasurementTypeID = (SELECT ID FROM MeasurementType WHERE Name = 'Current')) AS [Meter/IChannelCount], Asset.VoltageKV AS [Asset/Voltage], CONCAT((SELECT TOP 1 VALUE FROM Setting WHERE Name = 'PQBrowser.URL'),'/eventsearch?date=', FORMAT(Event.StartTime,'MM dd yyyy'),'&time=',FORMAT(Event.StartTime,'HH:mm:ss.000'), '&windowSize=10&timeWindowUnits=2&eventid=', Event.ID) AS [System/PQBrowserLink], (SELECT TOP 1 VALUE FROM Setting WHERE Name = 'Notifications.URL') AS [System/Notifications], ( SELECT DisturbanceType.Name AS Type, Phase.Name AS Phase, Disturbance.StartTime, Disturbance.EndTime, Disturbance.PerUnitMagnitude, Disturbance.DurationSeconds * 1000.0 AS DurationMilliseconds, Disturbance.DurationCycles, DisturbanceSeverity.SeverityCode AS Severity FROM Disturbance Worst JOIN Phase WorstPhase ON Worst.PhaseID = WorstPhase.ID AND WorstPhase.Name = 'Worst' JOIN Disturbance ON Disturbance.EventID = Worst.EventID AND Disturbance.EventTypeID = Worst.EventTypeID AND Disturbance.StartTime = Worst.StartTime AND Disturbance.EndTime = Worst.EndTime AND Disturbance.PerUnitMagnitude = Worst.PerUnitMagnitude AND Disturbance.PhaseID <> Worst.PhaseID JOIN EventType DisturbanceType ON Disturbance.EventTypeID = DisturbanceType.ID JOIN Phase ON Disturbance.PhaseID = Phase.ID LEFT OUTER JOIN DisturbanceSeverity ON DisturbanceSeverity.DisturbanceID = Disturbance.ID WHERE Disturbance.EventID = Event.ID ORDER BY Disturbance.StartTime FOR XML PATH('Disturbance'), TYPE ) [Disturbances], RIGHT(DataFile.FilePath, CHARINDEX(CHAR(92), REVERSE(DataFile.FilePath)) - 1) [File/Name] FROM Event JOIN EventType ON Event.EventTypeID = EventType.ID JOIN Meter ON Event.MeterID = Meter.ID JOIN Location ON Meter.LocationID = Location.ID JOIN Asset ON Event.AssetID = Asset.ID JOIN DataFile ON Event.FileGroupID = DataFile.FileGroupID AND ( DataFile.FilePath LIKE '%.DAT' OR DataFile.FilePath LIKE '%.D00' OR DataFile.FilePath LIKE '%.PQD' OR DataFile.FilePath LIKE '%.RCD' OR DataFile.FilePath LIKE '%.RCL' OR DataFile.FilePath LIKE '%.SEL' OR DataFile.FilePath LIKE '%.EVE' OR DataFile.FilePath LIKE '%.CEV' ) WHERE Event.ID = @eventID FOR XML PATH('EventDetail'), TYPE