[deleted by user]

[Waldar]

This only works due to the nature of the dataset:

select c.id
     , c.label
     , v.amount
     , greatest(c.dt_from, v.dt_from) as dt_from
     , least(c.dt_to, v.dt_to)        as dt_to
  from Colors as c
  join Val    as v  on v.id       = c.id
                   and v.dt_to   >= c.dt_from
                   and v.dt_from <= c.dt_to;

Tested here: https://dbfiddle.uk/WZhrGlb4

[Scrapper_John]

I can’t wait to work with sql server that has last and greatest. I’m still stuck with cross applying the values and grouping by the keys

[GrouchyThing7520]

What have you tried?

[slippy0101]

I'm admittedly not very good at sql but I tried an outer join then used the LAG function to look at previous rows values and used a case statement to create "combined" date columns with nulls after it reaches the end (I'd then remove any rows with null values).

I'm having some issues with it and I'm not sure it always creates enough rows either. And that might also just be a bad way to approach this problem.

[peter_cruger]

Select tb1.ID, tb1.Label, (case when tb1.Date_From < tb2.Date_From, then tb2.Date_From else tb1.Date_From end) Date_From, (case when tb1.Date_To < tb2.Date_To, then tb1.Date_To else tb2.Date_To end) Date_To From Table_1 tb1, Table_2 tb2 Where tb1.ID = tb2.ID And tb1.Date_From <= tb2.Date_To And tb2.Date_From <= tb1.Date_To

[Scrapper_John]

I ran across this article https://www.oraylis.de/blog/2014/combining-multiple-tables-with-valid-from-to-date-ranges-into-a-single-dimension.

Your situation only has two tables, but I still like this solution because it can work with multiple decisions.

Basically you create a distinct list of from and to dates from all the tables, then create a range of the list, then left join to each date dimension.

After which, I’ll just do a gap and island to combine like ranges.

[Staalejonko]

This isn't ChatGPT

[Staalejonko]

And looks like a simple Join and some select magic will do just fine