Using MySQL Workbench, is there a way to fill in missing dates and creating a new row with the last status that was filled
I currently have a massive table of all status changes for a specific ID with the corresponding date, and multiple IDs have the same data. Currently the Table looks like
id status date 1 open 2020-06-21 1 closed 2020-06-24 2 open 2020-06-23 2 closed 2020-06-25 And I am trying to get to this. Filling in the dates with the last status .
id status date 1 open 2020-06-21 1 open 2020-06-22 1 closed 2020-06-23 2 open 2020-06-22 2 open 2020-06-23 2 closed 2020-06-24 This table is always going to be updating constantly so I’m unsure how to best store the information, maybe a separate view? I’m also looking into using a calendar tables but that doesn’t seem to be the best solution(but if it is please tell me). Has anyone encountered a problem like this before and have a good solution that they have done in the past? Any help would be greatly appreciated.
If your version of mysql supports CTEs (quoting your version is useful when asking) then
WITH CTE as ( select s.id,dt from dates cross join (select distinct id from t) s where dt between (select min(date) from t where t.id = s.id) and (select max(date) from t where t.id = s.id) ) select cte.id,cte.DT , #t.id,t.DATE,t.status, coalesce(t.status, (select t1.status from t t1 where t1.id = cte.id and t1.date < cte.dt and t1.status is not null order by t1.date desc limit 1) ) status from cte left join t on cte.dt = t.date and t.id = cte.id order by cte.id,cte.dt; Where dates is my calendar table.
The cte part grabs the date range for each id and the main query backfills by looking for the most recent non null status per id if required (coalesce)
