Sql: Find sum of column from second table using date from first table

I’ve been struggling to build a query that calculate the sum of column called ‘TIDAL_VOLUME’ with respect to date value that’s coming from another table.

Please see the content of the Table_1:

Please see the content of the Table_2:

Note: TIDAL_VOLUME might have NULL as well.

Now, the start time for O2_Device value ‘Endotracheal tube’ is ‘2013-08-06 08:10:05’ for same HADM_ID and SUBJECT_ID. and end time is whenever new O2_Device value comes in. In this case which is ‘Nasal cannula’. Which means start time for ‘Endotracheal tube’ is ‘2013-08-06 08:10:05’ and end time is ‘2013-08-06 10:15:05’ for HADM_ID = 1 and SUBJECT_ID = 100.

Using that start time and end time criteria, I have to look for TIDAL_VALUE in Table_2. In this example it’s 700, 800. Ans for TIDAL_VOLUME is 1500.

Please see the resultant output look like this:

Thanks in advance.

Answer

If you can add End_Time to the first table, you can use BETWEEN when you join the tables.

SELECT t1.HADM_ID, t1.Subject_ID, t1.ChartTime, SUM(t2.tidal_volume) AS tidal_volume
FROM Table_1 AS t1
JOIN Table_2 AS t2 
    ON t1.HADM_ID = t2.HADM_ID 
        AND t1.Subject_ID = t2.Subject_ID 
        AND t2.ChartTime BETWEEN t1.ChartTime AND t1.End_Time
GROUP BY t1.HADM_ID, t1.Subject_ID, t1.ChartTime