MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Records: 2  Duplicates: 0  Warnings: 0

Records: 5  Duplicates: 0  Warnings: 0

WITH RECURSIVE dates ( date ) AS (
    SELECT DATE('2024-03-01')
    UNION ALL
    SELECT DATE(date) + INTERVAL 1 DAY
    FROM dates
    WHERE DATE(DATE) < (NOW() - INTERVAL 1 DAY)
),
current_history_data_query AS (
    SELECT 
        current_history_data.*
    FROM (
        SELECT
           DATE(timestamp) AS date,
           user_id,
           organisation_id,
           status_id,
           stopped_reason_id,
           dossier_created,
           'history-data' AS src
         FROM (
           SELECT
               oulsh.user_id,
               oulsh.organisation_id,
               oulsh.timestamp,
               oulsh.status_id,
               oulsh.stopped_reason_id,
               oul.dossier_created,
               ROW_NUMBER() OVER (PARTITION BY oulsh.user_id, oulsh.organisation_id, DATE(oulsh.timestamp) ORDER BY oulsh.timestamp DESC) AS row_num
           FROM organisation_user_link_status_history AS oulsh
           INNER JOIN organisation_user_link AS oul ON oulsh.user_id = oul.user_id AND oulsh.organisation_id = oul.organisation_id
         ) AS numbered_rows
         WHERE row_num = 1 AND DATE(timestamp) != DATE(NOW())
        
         UNION ALL
        
         SELECT CURRENT_DATE AS date, oul.user_id, oul.organisation_id, oul.status_id, oul.stopped_reason_id, oul.dossier_created, 'current-data' AS src
         FROM organisation_user_link AS oul
    ) AS current_history_data
    ORDER BY DATE DESC
)
SELECT d.date, u.user_id, u.organisation_id,
  (
    SELECT status_id
    FROM current_history_data_query
    WHERE user_id = u.user_id
    AND organisation_id = u.organisation_id
    AND date >= d.date
    ORDER BY date ASC
    LIMIT 1
  ) AS status_id,
  (
    SELECT stopped_reason_id
    FROM current_history_data_query
    WHERE user_id = u.user_id
    AND organisation_id = u.organisation_id
    AND date >= d.date
    ORDER BY date ASC
    LIMIT 1
  ) AS stopped_reason_id,
  (
    SELECT dossier_created
    FROM current_history_data_query
    WHERE user_id = u.user_id
    AND organisation_id = u.organisation_id
    AND date >= d.date
    ORDER BY date ASC
    LIMIT 1
  ) AS dossier_created
FROM dates d
JOIN (SELECT DISTINCT user_id, organisation_id FROM organisation_user_link) u
ORDER BY d.date DESC, u.user_id;

date	user_id	organisation_id	status_id	stopped_reason_id	dossier_created
2024-03-14	3	73	2	null	2021-10-29 07:50:21
2024-03-14	9	1199	4	5	2021-05-19 17:44:07
2024-03-13	3	73	2	null	2021-10-29 07:50:21
2024-03-13	9	1199	1	null	2021-05-19 17:44:07
2024-03-12	3	73	2	null	2021-10-29 07:50:21
2024-03-12	9	1199	1	null	2021-05-19 17:44:07
2024-03-11	3	73	1	null	2021-10-29 07:50:21
2024-03-11	9	1199	2	null	2021-05-19 17:44:07
2024-03-10	3	73	1	null	2021-10-29 07:50:21
2024-03-10	9	1199	2	null	2021-05-19 17:44:07
2024-03-09	3	73	1	null	2021-10-29 07:50:21
2024-03-09	9	1199	2	null	2021-05-19 17:44:07
2024-03-08	3	73	3	null	2021-10-29 07:50:21
2024-03-08	9	1199	2	null	2021-05-19 17:44:07
2024-03-07	3	73	3	null	2021-10-29 07:50:21
2024-03-07	9	1199	2	null	2021-05-19 17:44:07
2024-03-06	3	73	3	null	2021-10-29 07:50:21
2024-03-06	9	1199	2	null	2021-05-19 17:44:07
2024-03-05	3	73	4	3	2021-10-29 07:50:21
2024-03-05	9	1199	2	null	2021-05-19 17:44:07
2024-03-04	3	73	4	3	2021-10-29 07:50:21
2024-03-04	9	1199	2	null	2021-05-19 17:44:07
2024-03-03	3	73	4	3	2021-10-29 07:50:21
2024-03-03	9	1199	2	null	2021-05-19 17:44:07
2024-03-02	3	73	4	3	2021-10-29 07:50:21
2024-03-02	9	1199	2	null	2021-05-19 17:44:07
2024-03-01	3	73	4	3	2021-10-29 07:50:21
2024-03-01	9	1199	2	null	2021-05-19 17:44:07