How to check specific range of values in Array.

This is interesting logic to create rules for.. Just to confirm, this data is not stored in a database?  Typically I would do this type of calculation within a view on the DB side.

This is interesting logic to create rules for.. Just to confirm, this data is not stored in a database?  Typically I would do this type of calculation within a view on the DB side.

Basically this data is returned from integration call with complex structure and stored in DB later on so just to answer your question, Yes it is stored. Any idea about this logic? 

If you can calculate this based on your data store, that would be my preference.  This could be done in Appian however, with a little additional overhead. 

This an example with MSSQL, over table chris_test_table (id,date,code,amount)

SELECT
[id]
[date],
[code],
[amount],
[delayStart],
DATEDIFF(
	day,
	[delayStart],
	[date]
) 'daysDelayed'
FROM
(
	SELECT
	t.[id],
	t.[date],
	t.[code],
	t.[amount],
	CASE -- Return minimum 110 that is after the prior 113
		WHEN (t.[code] = 113) THEN -- End of Delay
			(
				SELECT min(date) from chris_test_table t2 where t2.date<t.date and code=110 and t2.date >= 
				CASE -- If no prior 113s, return first 110
					WHEN EXISTS (select * from chris_test_table t3 where t3.date<t.date and t3.code=113) THEN
						(select max(date) from chris_test_table t4 where t4.date<t.date and t4.code=113)
					ELSE 
						(select min(date) from chris_test_table t5 where code=110)
				END
			)
		ELSE null
	END as 'delayStart'
	FROM chris_test_table t
) a
ORDER BY [date] asc

This could be expanded as necessary and tied to a CDT in your application for easy access.

The output puts the final delay details on the lines with the 113 / closing event as:

Thx. Chris for this but this is not ideal for me as an additional requirement is needed for my calculation. I can have a case with no 113 events as the client has an active delay with no end.  Also, the time range where I need to check this is 1 year and on every chunk of delay data, I need to add + 15 days as this is a requirement, for the calculation as the delay starts 15 days before the first 110 event. 

Just to button up the SQL view solution here, you can certainly add 15 days to each delay and calculate a running delay by utilizing the current date (when no 113 present).

This adjustment assumes you want the longest delay in each calendar year based on the delay start date, which can be adjusted to end date as well.

SELECT
DATEPART(year,[date]) 'delayYear',
max(daysDelayed) 'delayDays'
FROM
(
	SELECT
	[id],
	[date],
	[code],
	[amount],
	[delayEnd],
	DATEDIFF(
		day,
		[date],
		[delayEnd]
	)+15 'daysDelayed'
	FROM
	(
		SELECT
		t.[id],
		t.[date],
		t.[code],
		t.[amount],
		CASE 
			WHEN (t.[code]=110) THEN
				CASE 
					WHEN EXISTS (select * from chris_test_table t2 where t2.[date]>t.[date] and t2.[code]=113) THEN 
						(select min(date) from chris_test_table t3 where t3.[date]>t.[date] and t3.[code]=113)
					ELSE 
						CAST(GETDATE() AS DATE)
				END
			ELSE null
		END as 'delayEnd'
		FROM chris_test_table t
	) a
)b
GROUP BY DATEPART(year,b.[date])

I added a few rows for 2021 code 110's with no ending 113, results for year and longest delay:

Thx Chris for this. The time range is -1 year from today that is the date interval to be checked. So -365 days from today. I misplaced this info.  

No worries, this update pulls only the maximum delay that has ended within the past year.  Change the commenting on the final WHERE clauses to flip to use delays that have started within the past year.  As, a delay could start 14 months ago and end 10 months ago.

SELECT
max(daysDelayed) 'delayDays'
FROM
(
	SELECT
	[id],
	[date],
	[code],
	[amount],
	[delayEnd],
	DATEDIFF(
		day,
		[date],
		[delayEnd]
	)+15 'daysDelayed'
	FROM
	(
		SELECT
		t.[id],
		t.[date],
		t.[code],
		t.[amount],
		CASE 
			WHEN (t.[code]=110) THEN
				CASE 
					WHEN EXISTS (select * from chris_test_table t2 where t2.[date]>t.[date] and t2.[code]=113) THEN 
						(select min(date) from chris_test_table t3 where t3.[date]>t.[date] and t3.[code]=113)
					ELSE 
						CAST(GETDATE() AS DATE)
				END
			ELSE null
		END as 'delayEnd'
		FROM chris_test_table t
	) a
	--WHERE a.[date]>=CAST(DATEADD(year,-1,GETDATE()) as DATE) -- Uses delay START
	WHERE a.[delayEnd]>=CAST(DATEADD(year,-1,GETDATE()) as DATE) -- Uses delay END
) b