Nearest Neighbor for Point in Time

Question

Say I have two tables (SQL Fiddle). One that has recorded values at various timestamps, the other indicates ID's and datetimes to sample for nearest values. Using something similar to Kevin Meade's NEAREST NEIGHBOR PREFERENCE LOW (but in SQL Server 2008), I want to find the (non-null value) of the indicated ID closest to the target (census) date but not after the (census) date. If there is a row that matches the census date, use that one (unless it has a null value). If there is no row that is before the census date then find the row that is closest to the census date but not before it and use that one.

First Table:

CREATE TABLE Recorded_Vent_Types
    ([PAT_ENC_CSN_ID] int, [RECORDED_TIME] datetime, [MEAS_VALUE] varchar(9));

INSERT INTO Recorded_Vent_Types
    ([PAT_ENC_CSN_ID], [RECORDED_TIME], [MEAS_VALUE])
VALUES
    (11117777,  '2013-06-08 19:36:00.000',  'SIMV/PRVC'),
    (11117777,  '2013-06-08 22:21:00.000',  'PRVC/AC'),
    (11117777,  '2013-06-09 00:10:00.000',  NULL),
    (11117777,  '2013-06-09 03:00:00.000',  'SIMV/PRVC'),
    (11117777,  '2013-06-09 23:59:00.000',  'SIMV/PRVC'),
    (11117777,  '2013-06-10 00:00:00.000',  'NAVA'),
    (11117777,  '2013-06-10 00:20:00.000',  'PS'),
    (11117777,  '2013-06-10 00:25:00.000',  NULL),
    (555999,    '2013-06-08 00:36:00.000',  NULL),
    (555999,    '2013-06-08 22:21:00.000',  'PRVC/AC'),
    (555999,    '2013-06-09 00:10:00.000',  'SIMV/PRVC'),
    (555999,    '2013-06-11 23:15:00.000',  'BIVENT'),
    (555999,    '2013-06-12 00:00:00.000',  NULL),
    (555999,    '2013-06-12 00:20:00.000',  'PS');

Second Table:

CREATE TABLE Census
    ([PAT_ENC_CSN_ID] int, [CENSUS_TIME] datetime);

INSERT INTO Census
    ([PAT_ENC_CSN_ID], [CENSUS_TIME])
VALUES
    (11117777, '2013-06-08 00:00:00'),
    (11117777, '2013-06-09 00:00:00'),
    (11117777, '2013-06-10 00:00:00'),
    (11117777, '2013-06-11 00:00:00'),
    (555999, '2013-06-08 00:00:00'),
    (555999, '2013-06-09 00:00:00'),
    (555999, '2013-06-11 00:00:00'),
    (555999, '2013-06-12 00:00:00');

Here's Mr Meade's Oracle Code for something similar within one table for given ID:

select *
from claim_history
where claim_id = 1
and status_date =
   (
    select min(status_date)
    from (
          select max(status_date) status_date
          from claim_history
          where claim_id = 1
          and status_date <= sysdate-3
          union all
          select min(status_date)
          from claim_history
          where claim_id = 1
          and status_date > sysdate-3
         )
   )
/

My Desired Result set:

PAT_ENC_CSN_ID    CENSUS_TIME           RECORDED_TIME               MEAS_VALUE
555999      June, 08 2013 00:00:00+0000 June, 08 2013 22:21:00+0000 PRVC/AC
555999      June, 09 2013 00:00:00+0000 June, 08 2013 22:21:00+0000 PRVC/AC
555999      June, 11 2013 00:00:00+0000 June, 09 2013 00:10:00+0000 SIMV/PRVC
555999      June, 12 2013 00:00:00+0000 June, 11 2013 23:15:00+0000 BIVENT
11117777    June, 08 2013 00:00:00+0000 June, 08 2013 19:36:00+0000 SIMV/PRVC
11117777    June, 09 2013 00:00:00+0000 June, 08 2013 22:21:00+0000 PRVC/AC
11117777    June, 10 2013 00:00:00+0000 June, 10 2013 00:00:00+0000 NAVA
11117777    June, 11 2013 00:00:00+0000 June, 10 2013 00:20:00+0000 PS

@Gordon Linoff gave me the idea to use absolute values of the date diff between census times and recorded times. This led me to modify @bobs solution here.

SELECT * FROM
    (
    SELECT rvt.PAT_ENC_CSN_ID, CENSUS_TIME, RECORDED_TIME, MEAS_VALUE, ABS(DATEDIFF(s, c.CENSUS_TIME, RECORDED_TIME)) diff,
        ROW_NUMBER() OVER (PARTITION BY rvt.PAT_ENC_CSN_ID, c.CENSUS_TIME ORDER BY ABS(DATEDIFF(s, c.CENSUS_TIME, RECORDED_TIME))) AS SEQUENCE
    FROM Recorded_Vent_Types rvt join Census c on rvt.PAT_ENC_CSN_ID=c.PAT_ENC_CSN_ID
    WHERE MEAS_VALUE IS NOT NULL
    ) as m
WHERE SEQUENCE = 1
ORDER BY PAT_ENC_CSN_ID,CENSUS_TIME
;

But this returns the (absolute) closest recorded time, with no preference given to a recorded time prior to the census time. Result:

PAT_ENC_CSN_ID    CENSUS_TIME           RECORDED_TIME               MEAS_VALUE
555999      June, 08 2013 00:00:00+0000 June, 08 2013 22:21:00+0000 PRVC/AC
555999      June, 09 2013 00:00:00+0000 June, 09 2013 00:10:00+0000 SIMV/PRVC
555999      June, 11 2013 00:00:00+0000 June, 11 2013 23:15:00+0000 BIVENT
555999      June, 12 2013 00:00:00+0000 June, 12 2013 00:20:00+0000 PS
11117777    June, 08 2013 00:00:00+0000 June, 08 2013 19:36:00+0000 SIMV/PRVC
11117777    June, 09 2013 00:00:00+0000 June, 08 2013 22:21:00+0000 PRVC/AC
11117777    June, 10 2013 00:00:00+0000 June, 10 2013 00:00:00+0000 NAVA
11117777    June, 11 2013 00:00:00+0000 June, 10 2013 00:20:00+0000 PS

Answer 1

You can do this as a correlated subquery -- in both Oracle and SQL Server, because this is almost standard SQL except for the top 1.

Here is the query:

select *,
       (select top 1 PAT_ENC_CSN_ID
        from census c
        where c.census_time <= rvt.recorded_time
        order by (case when c.census_time <= rvt.recorded_time then 1 else 0
                  end) desc,
                 (case when c.census_time <= rvt.recorded_time then c.census_time
                  end) desc,
                 c.census_time asc
       ) as nearestVal
from Recorded_Vent_Types rvt

The subquery returns one row, based on the order by, which is key to the query. It has three parts.

The first puts all census times before the recorded time at the beginning. The second sorts these by census time in descending order, the third sorts the rest by ascending time. I would like to replace the last two with:

abs(c.census_time - rvt.recorded_time)

Because this is logically what it does. Alas that doesn't work, because abs() doesn't work on datetime. And then I'd have to use the datediff() function or a case statement, and it would start to look more complicated.