Find if any row parameter value was equal to 1 in period of time

I have a problem constructing a query.

I had table like this:

Id	Start Date	Parameter Value
1	NULL	1
2	2023-10-01 00:00:00.0000000	0
3	2024-01-01 00:00:00.0000000	0

I need to find out if any row valid between 2023-01-01 and 2024-12-31 contains a parameter value equal to 1. In this case, the script should return true, because the first parameter whose start date is null is valid until 2023-09-30, i.e. it was valid in the first half of 2023. Do you know how to do it right?

Solution

As mentioned in the comments, you can use LEAD (or LAG) to get the value for the next row. I assign a arbitrary values for the first row's start date and the last row's end day ((0001-01-01 and (SYSDATETIME() respectively) to remove the NULL values. Then we can check if there is a row in the range. Depending on where this is, you might then also have AND C.ParameterValue = 1 and the query is in an EXISTS, but I've used conditional aggregation here to return a pseudo-boolean depending on if there is a relevant row or not:

WITH CTE AS(
    SELECT Id,
           ISNULL(V.StartDate,'0001-01-01') AS StartDate,
           LEAD(V.StartDate,1,SYSDATETIME()) OVER (ORDER BY V.StartDate) AS EndDate, --SYSDATETIME() ebcause I wanted an endvalue
           V.ParameterValue
    FROM (VALUES(1,CONVERT(datetime2(7),NULL),1),
                (2,CONVERT(datetime2(7),'2023-10-01 00:00:00.0000000'),0),
                (3,CONVERT(datetime2(7),'2024-01-01 00:00:00.0000000'),0))V(Id,StartDate,ParameterValue))
SELECT MAX(CASE C.ParameterValue WHEN 1 THEN 1 ELSE 0 END) AS InRange
FROM CTE C
WHERE C.EndDate >= '20230101'
  AND C.StartDate < '20250101';