By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE income(
point INT,
date VARCHAR(60),
inc FLOAT
);
CREATE TABLE outcome(
point INT,
date VARCHAR(60),
ou_t FLOAT
);
INSERT INTO income VALUES
(1, '2001-03-22', 15000.0000),
(1, '2001-03-23', 15000.0000),
(1, '2001-03-24', 3400.0000),
(1, '2001-04-13', 5000.0000),
(1, '2001-05-11', 4500.0000),
(2, '2001-03-22', 10000.0000),
(2, '2001-03-24', 1500.0000),
(3, '2001-09-13', 11500.0000),
(3, '2001-10-02', 18000.0000);
INSERT INTO outcome VALUES
(1, '2001-03-14 00:00:00.000', 15348.0000),
(1, '2001-03-24 00:00:00.000', 3663.0000),
(1, '2001-03-26 00:00:00.000', 1221.0000),
(1, '2001-03-28 00:00:00.000', 2075.0000),
(1, '2001-03-29 00:00:00.000', 2004.0000),
(1, '2001-04-11 00:00:00.000', 3195.0400),
(1, '2001-04-13 00:00:00.000', 4490.0000),
(1, '2001-04-27 00:00:00.000', 3110.0000),
(1, '2001-05-11 00:00:00.000', 2530.0000),
(2, '2001-03-22 00:00:00.000', 1440.0000),
(2, '2001-03-29 00:00:00.000', 7848.0000),
(2, '2001-04-02 00:00:00.000', 2040.0000),
Records: 9 Duplicates: 0 Warnings: 0
Records: 15 Duplicates: 0 Warnings: 0
SELECT distinct cast(date as date) dt
FROM
(SELECT *
FROM income
UNION ALL
SELECT *
FROM outcome) A
where point=1
order by dt
| dt |
|---|
| 2001-03-14 |
| 2001-03-22 |
| 2001-03-23 |
| 2001-03-24 |
| 2001-03-26 |
| 2001-03-28 |
| 2001-03-29 |
| 2001-04-11 |
| 2001-04-13 |
| 2001-04-27 |
| 2001-05-11 |
WITH RECURSIVE cte AS (
SELECT Min(mndate) mindt, MAX(mxdate) maxdt
FROM (SELECT MIN(date) AS mndate, MAX(date) AS mxdate
FROM outcome
UNION
SELECT MIN(date), MAX(date)
FROM income) v
UNION
SELECT mindt + INTERVAL 1 DAY, maxdt
FROM cte
WHERE mindt + INTERVAL 1 DAY <= maxdt),
cte2 AS (
SELECT point, CAST(mindt AS DATE) AS rdate
FROM cte
CROSS JOIN (SELECT DISTINCT point FROM outcome
UNION
SELECT DISTINCT point FROM income) p)
SELECT *
FROM cte2
LEFT JOIN outcome
ON cte2.point=outcome.point
AND cte2.rdate=outcome.date
LEFT JOIN income
ON cte2.point=income.point
AND cte2.rdate=income.date
WHERE cte2.point=1
AND COALESCE(outcome.date, income.date) IS NOT NULL
ORDER BY cte2.rdate
| point | rdate | point | date | ou_t | point | date | inc |
|---|---|---|---|---|---|---|---|
| 1 | 2001-03-14 | 1 | 2001-03-14 00:00:00.000 | 15348 | null | null | null |
| 1 | 2001-03-22 | null | null | null | 1 | 2001-03-22 | 15000 |
| 1 | 2001-03-23 | null | null | null | 1 | 2001-03-23 | 15000 |
| 1 | 2001-03-24 | 1 | 2001-03-24 00:00:00.000 | 3663 | 1 | 2001-03-24 | 3400 |
| 1 | 2001-03-26 | 1 | 2001-03-26 00:00:00.000 | 1221 | null | null | null |
| 1 | 2001-03-28 | 1 | 2001-03-28 00:00:00.000 | 2075 | null | null | null |
| 1 | 2001-03-29 | 1 | 2001-03-29 00:00:00.000 | 2004 | null | null | null |
| 1 | 2001-04-11 | 1 | 2001-04-11 00:00:00.000 | 3195.04 | null | null | null |
| 1 | 2001-04-13 | 1 | 2001-04-13 00:00:00.000 | 4490 | 1 | 2001-04-13 | 5000 |
| 1 | 2001-04-27 | 1 | 2001-04-27 00:00:00.000 | 3110 | null | null | null |
| 1 | 2001-05-11 | 1 | 2001-05-11 00:00:00.000 | 2530 | 1 | 2001-05-11 | 4500 |