By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH Projects AS (
SELECT 1 AS ID, 'Project1' AS Name, 100 AS invoiceTotal, 1 AS department UNION ALL
SELECT 2, 'Project2', 200, 3 UNION ALL
SELECT 3, 'Project3', 300, 2 UNION ALL
SELECT 4, 'Project4', 100, 2 UNION ALL
SELECT 5, 'Project5', 400, 1
),
Manning AS (
SELECT 1 AS ProjectID, 1547038287 AS timestamp UNION ALL
SELECT 1, 1515558287 UNION ALL
SELECT 1, 1501118287 UNION ALL
SELECT 5, 1471118287 UNION ALL
SELECT 5, 1481118287
),
cte AS (
SELECT p.ID, p.Name, p.invoiceTotal, m.timestamp,
ROW_NUMBER() OVER (PARTITION BY m.ProjectID ORDER BY m.timestamp DESC) rn
FROM Projects p
INNER JOIN Manning m
ON p.ID = m.ProjectID
WHERE m.timestamp BETWEEN 1301118287 and 1547038287
)
SELECT ID, Name, invoiceTotal, timestamp
FROM cte
WHERE rn = 1;
| ID | Name | invoiceTotal | timestamp |
|---|---|---|---|
| 1 | Project1 | 100 | 1547038287 |
| 5 | Project5 | 400 | 1481118287 |