Interpolate Next Value

CarlosJourdan

Hello all,

 

I'm doing some timeseries-oriented analysis, and one of the requirements that I have is to join on a secondary table to get the first event available after a date on the current table. 

My current solution involves using cte's and INTERPOLATE PREVIOUS VALUE with a reversed date column. Something similar to:

 

 

WITH primaryEvent AS
  (SELECT 
      key,
      date,
      value,
      DATEDIFF (dd, date, '19000101') reverseDate
    FROM someEventTable
  )
  ,
  secondaryEvent AS
  (SELECT 
      key,
      date,
      value,
      DATEDIFF (dd, date, '19000101') reverseDate
    FROM otherEventTable
  )
SELECT *
FROM primaryEvent
LEFT JOIN secondaryEvent
ON primaryEvent.key = secondaryEvent.key
  AND primaryEvent.reverseDate INTERPOLATE PREVIOUS VALUE secondaryEvent.reverseDate

 

This strikes me as an awful lot of code for something that could be solved very easily if there were a command such as INTERPOLATE NEXT VALUE, or at least if I could add the column logic into the INTERPOLATE PREVIOUS VALUE clause, avoiding the common table expressions (something like DATEDIFF(...,primaryEvent.reverseDate) INTERPOLATE PREVIOUS VALUE DATEDIFF(...,secondary.reverseDate) ).

 

Am I missing something obvious, or is my code the recommended way to go?

 

Regards

Carlos Jourdan