如何创建“催化剂”查看每个“ABC”的 z 时间戳之前 x 分钟和 z 时间戳之后 x 分钟的数据具体数据

Question

“我想要一份 SSRS 报告，该报告可以识别从商店收到 CNT 信号之前最后 5 分钟内收到的所有激活以及在 CNT 信号之后 5 分钟内收到的信号”

需要知道实现此目的的最佳实践是什么目标 - 谷歌如何修复我的查询，以便更容易在报告生成器中设计

我下面有 q 查询，但在 SSRS 中我无法将其分组或以有助于查看的方式扩展它 我有一个概念证明（图片），但不知道如何处理查询以获得结果，

这对于 Jnr 报告编写者来说有点雄心勃勃！

POC 催化剂理念 <<<<<<-

DECLARE @Division varchar(2) = '*',
@SubDivision varchar(4) = '*',
@MainArea varchar(3) = '*',
@SubArea varchar(3) = '*',
@StartD datetime = '2022-03-29',
@StartTime Varchar(10) = '06:00:00',
@EndD datetime = '2022-03-29',
@EndTime Varchar(10) = '18:00:00',
@X int = 5,
@Catylist varchar(3) = 'CNT'

DECLARE @Arc varchar(100),
@Svr varchar(50),
@db varchar(50),
@Str varchar(max) = '',
@Qry varchar(max) = '',
@mon varchar(100) = '',
@StartDate varchar(20),
@EndDate varchar(20)

SET @StartDate = CONVERT(varchar(11),@StartD,106) + ' ' + @StartTime
SET @EndDate = CONVERT(varchar(11),@EndD,106) + ' ' + @EndTime

SELECT @mon = IIF(UPPER(ServerName)=UPPER(@@ServerName),DatabaseName,'[' + ServerName + '].' + DatabaseName) + '.dbo.'
FROM maBranchModules WITH (NOLOCK) WHERE Module = 'MONITOR'



CREATE TABLE #FirstBL
(CustId int, Signl varchar(10), StartTime datetime, EndTime datetime,Seq int,Catalyst char(1))

DECLARE Arc_cursor CURSOR FOR
SELECT SQLServer, SQLDatabase FROM RW_ArchiveDTL WITH (NOLOCK)
WHERE StartDTTM >= DATEADD(month,-1,EOMONTH(@StartDate))
AND EndDTTM <= DATEADD(day,1,EOMONTH(@EndDate))
ORDER BY StartDTTM
OPEN Arc_cursor

FETCH NEXT FROM Arc_cursor INTO @Svr, @db

WHILE @@FETCH_STATUS = 0
BEGIN
IF @Svr = @@ServerName
SET @Arc = @db + '.dbo.'
ELSE
SET @Arc = '[' + @Svr + '].' + @db + '.dbo.'

IF @Str = ''
SET @Str = 'INSERT INTO #FirstBL (CustId,Signl,StartTime,Seq)'
ELSE
SET @Str = @Str + char(13) + 'UNION ALL '

/*GET ALL CATYLIST SIGNALS IN DATE RANGE*/
SET @Str = @Str + char(13) + 'SELECT
a.CustId, a.FirstAlarm,a.SignalTime,ROW_NUMBER() OVER (PARTITION BY a.CustId ORDER BY a.SignalTime) AS Seq
FROM ' + @Arc + 'ArchiveHistory AS a WITH (NOLOCK) INNER JOIN
' + @mon + 'Address AS b WITH (NOLOCK) ON a.AddressId = b.AddressId
WHERE a.SignalTime >= ''' + CONVERT(varchar,@StartDate,121) + '''
AND a.SignalTime < ''' + CONVERT(varchar,@EndDate,121) + '''
AND a.FirstAlarm = ''' + @Catylist + '''
AND a.CustId > 0 '
IF @Division <> '*'
SET @Str = @Str + char(13) + 'AND b.Division = ''' + @Division + ''' '
IF @SubDivision <> '*'
SET @Str = @Str + char(13) + 'AND b.SubDivision = ''' + @SubDivision + ''' '
IF @MainArea <> '*'
SET @Str = @Str + char(13) + 'AND a.MainArea = ''' + @MainArea + ''' '
IF @SubArea <> '*'
SET @Str = @Str + char(13) + 'AND a.SubArea = ''' + @SubArea + ''' '

IF @Qry <> ''
SET @Qry = @Qry + char(13) + 'UNION ALL '

/*GET ALL SIGNALS FOR @X MINUTES BEFORE & AFTER CATYLIST SIGNAL RECEIVED*/
SET @Qry = @Qry + char(13) + 'SELECT DISTINCT
--a.CustId, b.StartTime, a.SignalTime, a.FirstAlarm, b.EndTime,
a.CustId, a.SignalTime, a.FirstAlarm, a.OBNumber,
c.CustCode, c.CustDesc, d.Division, d.SubDivision, d.MainArea, d.SubArea, c.SuspendMode
FROM ' + @Arc + 'ArchiveHistory AS a WITH (NOLOCK) INNER JOIN
#FirstBL AS b WITH (NOLOCK) ON a.CustId = b.CustId INNER JOIN
' + @mon + 'Client AS c WITH (NOLOCK) ON a.CustId = c.CustId INNER JOIN
' + @mon + 'Address AS d WITH (NOLOCK) ON c.AddressId = d.AddressId
AND a.SignalTime BETWEEN DATEADD(minute,-' + CONVERT(varchar,@X) + ',b.StartTime) AND b.EndTime '

FETCH NEXT FROM Arc_cursor INTO @Svr, @db
END
CLOSE Arc_cursor;
DEALLOCATE Arc_cursor;

SET @Str = @Str + char(13) + 'ORDER BY CustId, SignalTime'
EXEC(@Str)

/*CLEAN OUT ALL EXTRA CATYLIST SIGNALS THAT FALLS WITHIN @X OF "FIRST"*/
UPDATE #FirstBL SET EndTime = DATEADD(minute,@X,StartTime) WHERE Seq = 1

UPDATE a SET a.Catalyst = IIF(a.StartTime BETWEEN b.StartTime AND b.EndTime,'N','Y')
FROM #FirstBL AS a INNER JOIN
#FirstBL AS b ON a.CustId = b.CustId
WHERE b.Seq = 1
AND a.Seq > 1

DELETE FROM #FirstBL WHERE Catalyst = 'N'

DECLARE @N int
SET @N = 1

WHILE (SELECT COUNT(*) FROM #FirstBL WHERE EndTime IS NULL) > 0
begin
UPDATE #FirstBL SET EndTime = DATEADD(minute,@X,StartTime), Seq = @N + 1
WHERE Seq = (SELECT TOP 1 a.Seq FROM #FirstBL a WHERE #FirstBL.CustId = a.CustId AND Seq > @N ORDER BY a.Seq)

UPDATE a SET a.Catalyst = IIF(a.StartTime BETWEEN b.StartTime AND b.EndTime,'N','Y')
FROM #FirstBL AS a INNER JOIN
#FirstBL AS b ON a.CustId = b.CustId
WHERE b.Seq = @N
AND a.Seq > @N

DELETE FROM #FirstBL WHERE Catalyst = 'N'

Set @N = @N +1
end

/*ALL TOGETHER NOW*/
SET @Qry = @Qry + char(13) + 'ORDER BY CustId, SignalTime'
EXEC(@Qry)

/*GET RID OF THE #*/
DROP TABLE #FirstBL

Answer 1

如果您想在5分钟内和提前观察，请建议使用申请。例如，以下是如何在5分钟内

使用应用程序查找事件+/ 5分钟内找到“ CNT”事件的每场比赛的方法

DROP TABLE IF EXISTS #Data 

CREATE TABLE #Data (
    ID INT IDENTITY(1,1) PRIMARY KEY
    ,SignalDateTime DATETIME2(0)
    ,FirstAlarm CHAR(3)
)

INSERT INTO #Data VALUES 
 ('2022-03-29 10:06','LRQ')
,('2022-03-29 10:07','CNF')
,('2022-03-29 10:07','CNT')
,('2022-03-29 10:07','DNT')
,('2022-03-29 13:19','LRQ')
,('2022-03-29 13:20','CNF')
,('2022-03-29 13:20','CNT')
,('2022-03-29 13:20','DNT')
,('2022-03-29 13:24','LRQ')
,('2022-03-29 13:24','CNF')
,('2022-03-29 13:24','CNT')
,('2022-03-29 13:24','DNT')
,('2022-03-29 13:25','CNF')
,('2022-03-29 13:25','CNT')
,('2022-03-29 13:25','DNT')
,('2022-03-29 14:31','LRQ')
,('2022-03-29 14:31','CNF')
,('2022-03-29 14:31','CNT')
,('2022-03-29 14:31','DNT')

SELECT CNTGroupID = DENSE_RANK() OVER (ORDER BY A.ID)
    ,*
FROM #Data AS A
CROSS APPLY (   
    SELECT *
    FROM #Data AS DTA
    WHERE DTA.FirstAlarm <> 'CNT'
    AND DTA.SignalDateTime BETWEEN DATEADD(mi,-5,A.SignalDateTime) AND DATEADD(mi,5,A.SignalDateTime)
) AS B
WHERE A.FirstAlarm = 'CNT'