Get Value in Date Range with date parameter

Question

Given 2 tables:

Table A

|id|datefrom  |dateto     |value|
|--|--------  |------     |-----|
|1 |2021-01-01|2021-01-07 |   7 |
|2 |2021-01-08|2021-01-15 |   9 |
|3 |2021-01-16|2021-01-23 |   4 |

Table B

|id|transdate  |value|
|--|--------   |-----|
|1 |2021-01-03 |   3 |
|2 |2021-01-10 |   6 |
|3 |2021-01-20 |   3 |

I want to produce the following View:

|id|transdate |B Value | A Value| AxB Value |
|--|--------- |--------|--------|-----------|
|1 |2021-01-03|    3   |    7   |     21    |
|2 |2021-01-10|    6   |    9   |     54    |
|3 |2021-01-20|    3   |    4   |     12    |

My Question, how to get Table A Value inside of that view, based on that transaction date within Table A Date From and Date To?

Answer 1

Use between to match the date ranged in A:

select
  b.id,
  b.transdate,
  b.value as b_value,
  a.value as a_value,
  b.value * a.value as a_x_b_value
from table_b b
join table_a a on a.id = b.id 
  and b.transdate between a.datefrom and a.dateto

If there is a possibility there won't be a matching row in A, use a left join with a default of (say) 1 for the A value:

select
  b.id,
  b.transdate,
  b.value as b_value,
  a.value as a_value,
  b.value * coalesce(a.value, 1) as a_x_b_value
from table_b b
left join table_a a on a.id = b.id
  and b.transdate between a.datefrom and a.dateto

Answer 2

SELECT
        a.id,
        b.transdate,
        DATE_PART('day', b.transdate - a.datefrom) "B Value",
        DATE_PART('day', a.dateto - a.datefrom) "A Value",
        (DATE_PART('day', a.dateto - a.datefrom) * DATE_PART('day', b.transdate - a.datefrom)) "AxB Value"
FROM table_a a
JOIN table_b b ON a.id = b.id

In PostgreSQL, if you subtract one datetime value from another you will get an INTERVAL in the form ddd days hh:mi:ss. You can then use the DATE_PART function to extract the number of full days in the interval.