SQL query with calculated dynamic min max

Question

I got an sql issue. I have two tables which look like this:

first TABLE X    second TABLE Y

TabX_ID|  DATE | Value Z    TabY_ID|TabX_ID | DATE | Value X | Value Y
4711   | 15.01 |    12         1   | 4711   | 15.01|  123    |  876
4711   | 20.01 |    5          2   | 4711   | 16.01|  12     |  54
4711   | 25.01 |    67         3   | 4711   | 17.01|  23     |  38
                               4   | 4711   | 20.01|  56     |  13
                               5   | 4711   | 23.01|  1      |  5

I need to assing all the data from TABLE Y to the data in the TABLE X DATE to the fitting timeframe.

I cant use a simple min - max because it changes.

1. DATE min 15.01 DATE-max:19.01
2. DATE-min:20.01 DATE-max:24.01
3. DATE-min:25.01 DATE-max:... 

So it looks like this

                     1 | 15.01 | 123 | 876
4711 | 15.01 | 12 -> 2 | 16.01 | 12  | 54    
                     3 | 17.01 | 23  | 38     

4711 | 20.01 | 5   -> 4 | 20.01 | 56  | 13
                      5 | 23.01 | 1   | 5

First I need to perform calculations with the TABLE Y VALUES X an Y and after that I need the VALUE Z from TABLE X. So it looks like this:

 ID  | DATE  | Calculated_Val
 4711| 15.01 | 345
 4711| 20.01 | 892

Is there a way to do this?

thx in advance

Answer 1

Not sure about MySQL but if you are doing this with Oracle, I would use the LEAD analytic function to get the next date value in the future in tableX and then join that to tableY.

An example of this would be:

select
  tabX_id,
  date_val as min_date,
  next_date_val as max_date,
  valueZ,
  valueX,
  valueY,
  y.date_val as tabY_date
from (
  select
    tabX_id,
    date_val,
    lead(date_val) over (partition by tabx_id order by date_val) 
      as next_date_val,
    valueZ
  from tabX
) x
join tabY y on (x.tabX_id = y.tabX_id and 
                y.date_val >= x.date_val and 
                (x._next_date_val is null or y.date_val < x.next_date_val))

Note that I haven't modified the next value of the date so am using a less-than condition. This is probably appropriate if you have a time component in any of the date fields but might not be exactly what you want if they are just date value.

Answer 2

This is a simple join and group by:

 select x.TabX_ID, y.DATE, min(ValueX), min(ValueY)
 from TableX x
  join TableY y
    on x.TabX_ID = y.TabX_ID
   and x.DATE = y.DATE
 group by x.TabX_ID, y.DATE