Open Bobby-Miller opened 1 month ago
Let me know if this works - if so, I'll move it to TIGA-R (or you can/I can show you how to build a record)
Followed a similar concept in the named query that shows the % of measurement records, # of alarms and # of events. It shows it like M:100% | A:8 | E:19.
The SQL query:
DECLARE @startDate DATE = :startDate;
DECLARE @endDate DATE = :endDate;
DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);
WITH DateList AS (
SELECT DISTINCT CAST(thr.RecordTime AS DATE) AS RecordDate
FROM tblAsiEFMHistoryRecords thr
WHERE CAST(thr.RecordTime AS DATE) BETWEEN @startDate AND @endDate
)
SELECT @columns = STRING_AGG(QUOTENAME(CONVERT(VARCHAR, RecordDate, 101)), ', ') WITHIN GROUP (ORDER BY RecordDate)
FROM DateList;
DECLARE @specificMeterIDs NVARCHAR(MAX) = :selectedMeters
SET @sql = '
SELECT *
FROM (
SELECT
thr.MeterID,
thr.RecordSource AS Device,
tdp.Protocol,
CONVERT(VARCHAR, CAST(thr.RecordTime AS date), 101) AS [Date],
''M:'' + CAST(ROUND(CAST(COUNT(DISTINCT DATEPART(hour, thr.RecordTime)) AS FLOAT) / 24 * 100, 2) AS VARCHAR(5)) + ''% | '' +
''A:'' + CAST(ISNULL(alarms.AlarmCount, 0) AS VARCHAR(10)) + '' | '' +
''E:'' + CAST(ISNULL(events.EventCount, 0) AS VARCHAR(10)) AS CombinedData
FROM tblAsiEFMHistoryRecords thr
INNER JOIN tblLookupDeviceProtocol tdp ON thr.RecordSource = tdp.device
LEFT JOIN (
SELECT
MeterID,
CAST(RecordTime AS DATE) AS AlarmDate,
COUNT(*) AS AlarmCount
FROM tblAsiEFMAlarmRecords
GROUP BY MeterID, CAST(RecordTime AS DATE)
) alarms ON thr.MeterID = alarms.MeterID AND CAST(thr.RecordTime AS DATE) = alarms.AlarmDate
LEFT JOIN (
SELECT
MeterID,
CAST(RecordTime AS DATE) AS EventDate,
COUNT(*) AS EventCount
FROM tblAsiEFMEventRecords
GROUP BY MeterID, CAST(RecordTime AS DATE)
) events ON thr.MeterID = events.MeterID AND CAST(thr.RecordTime AS DATE) = events.EventDate
WHERE CAST(thr.RecordTime AS date) BETWEEN ''' + CONVERT(VARCHAR, @startDate, 23) + ''' AND ''' + CONVERT(VARCHAR, @endDate, 23) + '''
AND thr.MeterID IN (' + @specificMeterIDs + ')
GROUP BY thr.MeterID, thr.RecordSource, CAST(thr.RecordTime AS date), tdp.Protocol, alarms.AlarmCount, events.EventCount
) AS SourceTable
PIVOT (
MAX(CombinedData)
FOR [Date] IN (' + @columns + ')
) AS PivotTable;';
EXEC sp_executesql @sql;
Proposed record update stored procedure solution
Note: this is just a proposed
guts
of a SQL stored procedure. Look up the syntax to do this properly.Also note: I'm only assuming you can do a count from multiple tables. This may not be the case. If not, you'll need to build a proc for each table (boo)
before
after