I have two tables
trade_table as
| id | start_date | transacted_date |
|---|---|---|
| A1 | 2022-02-14 | 2022-02-17 |
| A1 | 2022-02-17 | 2022-02-25 |
| A5 | 2022-02-15 | 2022-02-19 |
| A6 | 2022-02-21 | NULL |
and trading_days as
| trade_date |
|---|
| 2022-02-14 |
| 2022-02-15 |
| 2022-02-16 |
| 2022-02-17 |
| 2022-02-19 |
| 2022-02-21 |
| 2022-02-23 |
| 2022-02-25 |
How to get actual date difference from trade_date table based on values from transacted_date and start_date from trade_table.
Expected Output table
| id | start_date | transacted_date | transact_in_days |
|---|---|---|---|
| A1 | 2022-02-14 | 2022-02-17 | 3 |
| A1 | 2022-02-17 | 2022-02-25 | 4 |
| A2 | 2022-02-15 | 2022-02-19 | 3 |
| A6 | 2022-02-21 | NULL | null |
CodePudding user response：
In oracle can you try so:
select a.*,
(
select decode(count(*)-1,-1,null,count(*)-1)
from trading_days
where nvl(trade_date, '0001-01-01')
between a.start_date and a.transacted_date
) as transact_in_days
from trade_table a;
ID START_DATE TRANSACTED TRANSACT_IN_DAYS
-- ---------- ---------- ----------------------------------------
A1 2022-02-14 2022-02-17 3
A1 2022-02-17 2022-02-25 4
A5 2022-02-15 2022-02-19 3
A6 2022-02-21 null null
CodePudding user response：
select id,start_date,transacted_date, CASE WHEN transacted_date IS NULL THEN NULL ELSE (select count(1)-1 from trading_days where trade_date between start_date and transacted_date) END as transact_in_days from trade_table
