By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE likes (
"item_no" INTEGER,
"like_date" DATE
)
INSERT INTO likes VALUES
(95057, '2022-08-12'),
(95057, '2022-08-12'),
(95057, '2022-08-12'),
(95057, '2022-09-18'),
(95057, '2022-09-18'),
(95057, '2022-09-23'),
(95057, '2022-10-05'),
(95057, '2022-10-06')
8 rows affected
CREATE TABLE collection (
"item_no" INTEGER NOT NULL, -- Add FK constraint if you wish
"collection_type" VARCHAR(10) NOT NULL,
"placement_date" DATE NOT NULL,
PRIMARY KEY("item_no", "placement_date")
)
CREATE VIEW collection_scd2 AS
SELECT
*,
COALESCE(
DATEADD(
DD,
-1,
LEAD(placement_date) OVER (PARTITION BY item_no ORDER BY placement_date ASC)
),
GETDATE()
) AS "valid_to"
FROM collection
INSERT INTO collection VALUES
(95057, 'AB', '2022-05-01'),
(95057, 'FR', '2022-06-02'),
(95057, 'GT', '2022-07-03'),
(95057, 'HJ', '2022-08-04'),
(95057, 'JU', '2022-08-09'),
(95057, 'NH', '2022-08-11'),
(95057, 'BG', '2022-08-22'),
(95057, 'VF', '2022-09-01'),
(95057, 'CD', '2022-09-05'),
(95057, 'XS', '2022-09-14'),
(95057, 'AZ', '2022-09-15'),
(95057, 'PL', '2022-10-01'),
(95057, 'OK', '2022-10-11'),
(95057, 'IJ', '2022-10-02')
14 rows affected
SELECT
likes.item_no,
likes.like_date,
coll.collection_type,
coll.placement_date
FROM likes
JOIN collection_scd2 AS coll
ON likes.item_no = coll.item_no
AND likes.like_date BETWEEN coll.placement_date AND coll.valid_to
| item_no | like_date | collection_type | placement_date |
|---|---|---|---|
| 95057 | 2022-08-12 | NH | 2022-08-11 |
| 95057 | 2022-08-12 | NH | 2022-08-11 |
| 95057 | 2022-08-12 | NH | 2022-08-11 |
| 95057 | 2022-09-18 | AZ | 2022-09-15 |
| 95057 | 2022-09-18 | AZ | 2022-09-15 |
| 95057 | 2022-09-23 | AZ | 2022-09-15 |
| 95057 | 2022-10-05 | IJ | 2022-10-02 |
| 95057 | 2022-10-06 | IJ | 2022-10-02 |