DECLARE @eventID INT = {0}
DECLARE @url VARCHAR(MAX) = (SELECT Value FROM Setting WHERE Name = 'System.URL')

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],
	MeterLocation.AssetKey AS [Station/Key],
	MeterLocation.Name AS [Station/Name],
	Meter.AssetKey AS [Meter/Key],
	Meter.Name AS [Meter/Name],
	Line.VoltageKV AS [Line/Voltage],
	(
		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],
    @url AS [PQDashboard]
FROM
    Event JOIN
	EventType ON Event.EventTypeID = EventType.ID JOIN
	Meter ON Event.MeterID = Meter.ID JOIN
	MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN
    Line ON Event.LineID = Line.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