Hi all.
I am trying to understand this T-SQL into more of an English understanding so I can convert it into a server side service/business logic but struggling to understand.
Even if there is an easier T-SQL equiv, this would be much appreciated.
DECLARE @recurrence INT
, @interval INT
, @dollarAmount DECIMAL(32,18)
, @recurrenceCalc INT;
--Looping parameters
DECLARE @monitoringSetId INT
-- Define monitoring set
SET @monitoringSetId = 1;
SET @recurrence = 5;
-- What is the interval for which we measure transactions (seconds)
SET @interval = 240;
SET @dollarAmount = 100;
SET @recurrenceCalc = @recurrence - 1;
-- Get records to evaluate
;WITH rowBack AS (
SELECT FDE.[EquipmentId]
, FDE.[LastCheckedDataSubsetRecId]
, T.[DataSubsetRecId]
, ROW_NUMBER() OVER (PARTITION BY T.[EquipmentId] ORDER BY T.[DataSubsetRecId]) AS RowNum
FROM [Fde].[CustomerTerminal] FDE
LEFT OUTER JOIN #transactions_stage T
ON T.[EquipmentId] = FDE.[EquipmentId]
WHERE (
T.[AmountDispensed] = 0
OR T.[AmountDispensed] >= @dollarAmount
)
)
, checkBack AS (
SELECT rb.[EquipmentId]
, rb.[LastCheckedDataSubsetRecId]
, rb.[DataSubsetRecId]
, rb.[RowNum] AS RowNum1
, rb2.[DataSubsetRecId] AS CheckBackToID
FROM rowBack rb
LEFT OUTER JOIN rowBack rb2
ON rb2.[EquipmentId] = rb.[EquipmentId]
AND rb2.[RowNum] = rb.[RowNum] - (@recurrenceCalc - 1)
WHERE rb.[DataSubsetRecId] = rb2.[LastCheckedDataSubsetRecId]
)
-- Recursive CTE that simply generates one row for each recurrence we are trying to detect
, rowNumbers AS (
SELECT 0 AS RowNumber
UNION ALL
SELECT RowNumber + 1
FROM rowNumbers
WHERE RowNumber < @recurrenceCalc
)
-- Assign each transaction a row number
, ranges AS (
SELECT T.[DataSubsetRecId]
, T.[EquipmentId]
, T.[DateTimeStart]
, ROW_NUMBER() OVER (PARTITION BY T.[EquipmentId] ORDER BY T.[DateTimeStart]) AS RowNumber
FROM #transactions T
INNER JOIN [Fde].[CustomerTerminal] FDE
ON FDE.[EquipmentId] = T.[EquipmentId]
LEFT OUTER JOIN checkBack CB
ON CB.[EquipmentId] = T.[EquipmentId]
LEFT OUTER JOIN [Fde].[ActivityLog] AL
ON AL.[DataSubsetRecId]= T.[DataSubsetRecId]
WHERE (CB.[CheckBackToID] IS NULL OR T.[DataSubsetRecId] >= CB.[CheckBackToID])
AND AL.[DataSubsetRecId] IS NULL
AND (T.[AmountDispensed] = 0 OR T.[AmountDispensed] >= @dollarAmount)
)
SELECT T.[DataSubsetRecId]
, T.[EquipmentId]
, T.[TerminalSequence]
, T.[DateTimeStart]
, T.[TransactionName]
, T.[AmountDispensed]
, T.[CardNumberLast4]
, @monitoringSetId AS [MonitoringSetId]
FROM (
SELECT DISTINCT r2.[DataSubsetRecId]
FROM ranges r1
CROSS JOIN rowNumbers rn
INNER JOIN ranges r2
ON r2.[EquipmentId] = r1.[EquipmentId]
AND r2.[RowNumber] = r1.[RowNumber] + rn.[RowNumber]
WHERE EXISTS (
SELECT NULL
FROM ranges r3
WHERE r3.[EquipmentId] = r1.[EquipmentId]
AND r3.[RowNumber] = r1.[RowNumber] + @recurrenceCalc
AND r3.[DateTimeStart] <= DATEADD(SECOND, @interval, r1.[DateTimeStart])
)
) a
INNER JOIN #transactions T
ON T.[DataSubsetRecId] = a.[DataSubsetRecId]
INNER JOIN [Fde].[CustomerTerminal] FDE
ON FDE.[EquipmentId] = T.[EquipmentId]
INNER JOIN [Fde].[CustomerMonitoringSet] MS
ON MS.[MasterAccount] = FDE.[MasterAccount]
WHERE MS.[MonitoringSetId] = @monitoringSetId
ORDER BY T.[EquipmentId]
, T.[DateTimeStart];