Get Value in Date Range with date parameter-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

This query will match each table_a entry to every table_b transaction, based only on the transaction date being BETWEEN datefrom and dateto. table_a.id is ignored.

SELECT
    table_b.id,
    table_b.transdate,
    table_b.value AS "b_value",
    table_a.value AS "a_value",
    table_a.value * table_b.value AS "ab_value"
FROM table_b
LEFT JOIN table_a
    ON table_b.transdate BETWEEN table_a.datefrom AND table_a.dateto;

Output:

| id  | transdate                | b_value | a_value | ab_value |
| --- | ------------------------ | ------- | ------- | -------- |
| 1   | 2021-01-03T00:00:00.000Z | 3       | 7       | 21       |
| 2   | 2021-01-10T00:00:00.000Z | 6       | 9       | 54       |
| 3   | 2021-01-20T00:00:00.000Z | 3       | 4       | 12       |

db-fiddle here

CodePudding user response：

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.