SQL query of specific rows based on comparison of multiple date columns

I need to query rows out of a single table based on a comparison of multiple columns and multiple dates and group the results by a specific column.

The table has this basic structure:

engine	read_date	cyl1_read	cyl2_read	cyl1_last_maint_date	cyl2_last_maint_date
1	06/31/2023	25	21	07/15/2023	05/25/2023
1	11/01/2023	27	19	07/15/2023	05/25/2023
2	04/11/2023	37	18	05/15/2023	05/25/2023
2	08/27/2023	19	12		06/01/2023

For each engine, I need to query the cyl1_read and cyl2_read values that were read after the most recent corresponding cyl last maintenance date, if the corresponding cyl last maint date exist, otherwise get all the cyl read values.

So, for this example the result would be:

engine	read_date	cyl1_read	cyl2_read
1	06/31/2023		21
1	11/01/2023	27	19
2	08/27/2023	19	12

Since the cyl1_last_maint_date for engine 1 was on 7/15/2023, the cyl1_read that was read on 06/31/2023 would not be included, but the cyl2_read value that was read on 6/31/2023 would be included because the cyl2_last_maint_date (05/25/2023) was before the read date. The engine 2 values captured on 04/11/2023 would not be included because they were read before the last maint dates for both cyl1 and cyl2. The last engine 2 values read on 08/27/2023 would be included because there is no last maint date for cyl1 and the cyl2 last maint date is before the cyl2 last read date.

How can I accomplish this SQL query? I am running SQL Server 2016

Solution

I think this is easiest if you take a 2-step process

First, 'remove' any future reads from each row
Second, report on rows that have at least one value for the reads

It is possible to do this in one step, but I think it's much cleaner and clearer to do it in two.

Assuming the data is in the table #EngineData, The following SQL fragment only includes non-future reads

SELECT  engine, read_date, 
        CASE WHEN cyl1_last_maint_date <= read_date OR cyl1_last_maint_date IS NULL THEN cyl1_read ELSE NULL END AS cyl1_last_read,
        CASE WHEN cyl2_last_maint_date <= read_date OR cyl2_last_maint_date IS NULL THEN cyl2_read ELSE NULL END AS cyl2_last_read
FROM    #EngineData;

Results

engine      read_date  cyl1_last_read cyl2_last_read
----------- ---------- -------------- --------------
1           2023-06-30 NULL           21
1           2023-11-01 27             19
2           2023-04-11 NULL           NULL
2           2023-08-27 19             12

Then we can simply select from this where the last read for either cyl1 or cyl2 have a value (e.g., both not NULL). The initial step is in the CTE below, and the WHERE clause then removes the rows with no reads.

WITH PrevOnly_EngineData AS
        (SELECT engine, read_date, 
                CASE WHEN cyl1_last_maint_date <= read_date OR cyl1_last_maint_date IS NULL THEN cyl1_read ELSE NULL END AS cyl1_last_read,
                CASE WHEN cyl2_last_maint_date <= read_date OR cyl2_last_maint_date IS NULL THEN cyl2_read ELSE NULL END AS cyl2_last_read
        FROM    #EngineData
        )
    SELECT  *
    FROM    PrevOnly_EngineData
    WHERE   cyl1_last_read IS NOT NULL
            OR cyl2_last_read IS NOT NULL
    ORDER BY engine, read_date;

Results

engine      read_date  cyl1_last_read cyl2_last_read
----------- ---------- -------------- --------------
1           2023-06-30 NULL           21
1           2023-11-01 27             19
2           2023-08-27 19             12

You can see these on the following db<>fiddle. (Note - I replaced date 2023-06-31 with 2023-06-30).