" I want a SSRS report that can identify all activations received within the last 5 minutes before the CNT signal was received from a store and signals that were received within 5 minutes after the CNT signal "
Need to know what is the best practice to achieve this goal - what to google to fix my query to make it easier to design in report builder
I have q query below but within SSRS I cant group it or expand it in a way useful for viewing I have a proof of concept (image) but don't know how to approach the query to get to the result
a bit ambitious for a Jnr report writer!
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
CodePudding user response：
If you want to look behind and ahead 5 minutes, recommend just using APPLY. For example, here's how to find every match to a "CNT" event within 5 minutes
Using APPLY to Find Events /- 5 Minutes
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'