SQL query to find max value for each day available, and fill in rows for missing days to build a complete table?

I have a table from IBM Maximo called wostatus, which records changes in workorder statuses. There can be mutliple status changes in a single day, and changes are only recorded when they happen.

I need to be able to return a table that shows fills in the missing days in the table with the last known status, going back 2 years.

An example table looks like:

wonum	changedate	wostatus	wostatusid
WO00001	21/3/2023 10:56	APPR	100012
WO00001	21/3/2023 10:58	WAPPR	100014
WO00002	22/3/2023 11:03	APPR	100007
WO00002	24/3/2023 10:56	COMP	100009
WO00003	21/3/2023 10:59	WAPPR	100016
WO00003	21/3/2023 11:01	APPR	100017
WO00003	24/3/2023 10:56	WSCH	100018

I was trying to get a table that would show:

date	wonum	wostatus
21/03/2023	WO00001	WAPPR
21/03/2023	WO00003	APPR
22/03/2023	WO00001	WAPPR
22/03/2023	WO00003	APPR
22/03/2023	WO00002	APPR
23/03/2023	WO00001	WAPPR
23/03/2023	WO00003	APPR
23/03/2023	WO00002	APPR
24/03/2023	WO00001	WAPPR
24/03/2023	WO00003	WSCH
24/03/2023	WO00002	COMP

I have tried (without trying to fill in the missing rows) to get a work order to show me the maximum status on each day using max(changedate), and max(wostatusid). But I know enough to know I'm out of my depth here. Any help would be very much appreciated.

We use SQL Server.

Solution

This kind of problem is usually called gaps and islands, and there are quite a lot of examples here in stackoverflow, but i guess one more won't hurt:

;WITH CTE AS (
SELECT  TOP 1 WITH ties wonum, CONVERT(DATE, changedate , 103) AS date
,   changedate
, wostatus
FROM    (
    VALUES  (N'WO00001', N'21/3/2023 10:56', N'APPR', 100012)
    ,   (N'WO00001', N'21/3/2023 10:58', N'WAPPR', 100014)
    ,   (N'WO00002', N'22/3/2023 11:03', N'APPR', 100007)
    ,   (N'WO00002', N'24/3/2023 10:56', N'COMP', 100009)
    ,   (N'WO00003', N'21/3/2023 10:59', N'WAPPR', 100016)
    ,   (N'WO00003', N'21/3/2023 11:01', N'APPR', 100017)
    ,   (N'WO00003', N'24/3/2023 10:56', N'WSCH', 100018)
) t (wonum,changedate,wostatus,wostatusid)
ORDER BY row_number() OVER(partition BY wonum, CONVERT(date, changedate , 103)  ORDER BY CONVERT(datetime, changedate , 103) DESC)
)
, CTE2 AS (
    SELECT  *
    FROM    (
        VALUES(1),(2),(3),(4), (5), (6), (7),(8), (9), (10)
    ) v(c)
)
SELECT  a.*
FROM    (
    SELECT  *
    ,   DATEDIFF(DAY, LAG(date) OVER(PARTITION BY wonum ORDER BY date), date) AS prevDateDiff
    ,   LAG(wostatus) OVER(PARTITION BY wonum ORDER BY date) AS prevStatus
    ,   LEAD(wostatus) OVER(PARTITION BY wonum ORDER BY date) AS nextStatus
    FROM    cte
    ) dates
CROSS APPLY (
    SELECT  dates.wonum, dates.wostatus, dates.date, 0 AS calculatedFlag
    UNION ALL
    SELECT  dates.wonum, dates.prevStatus, DATEADD(DAY, -counter, dates.date), 1 AS calculatedFlag
    FROM    (
        select ROW_NUMBER() OVER( ORDER BY @@SPID) AS counter
        from CTE2 c
        cross apply CTE2 c2
        cross apply CTE2 c3
        ) c
    WHERE   c.counter < prevdatediff
    UNION ALL
    SELECT  dates.wonum, dates.wostatus, DATEADD(DAY, c.counter, dates.date), 2 AS calculatedFlag
    FROM    (
        select ROW_NUMBER() OVER( ORDER BY @@SPID) AS counter
        from CTE2 c
        cross apply CTE2 c2
        cross apply CTE2 c3
        ) c
    WHERE   c.counter < DATEDIFF(day, dates.date, GETDATE())
    AND nextStatus IS NULL -- Last workorder
    ) a

Here's one solution to the issue, it's a bit complex but i'll try to explain:

First i want to get only the last date per worknumber and day. I use Top 1 with Ties and order by row_number to get only the last date
Secondly, since we need to fill out dates, we want to have some sort of series. Usually your database might have a calender table that contains all dates, but maybe you don't, so i'm using a little apply thing that creates it.
Third, we have 3 cases
- The actual worknum date
- Fill dates backwards from worknum dates
- Fill dates forward to today's date
For each of the above cases, i create a separate UNION ALL inside the CROSS APPLY.
- First case is easiest, just take the data we have
- Second case, we want to use our series tables to generate a list of rows for every date that is different from previous dates.
- Third case is similar to second, but instead we fill up forward. Important to only fill up for the last workorder date, for that i use LEAD(wostatus) to make sure there are no more workorder dates in the future.

So yeah, it looks a bit involved, but maybe it's possible to understand, feel free to add questions and i'll try to explain a bit more in detail

EDIT:

To just use your table, change this part:

FROM    yourworktable t
/*(
    VALUES  (N'WO00001', N'21/3/2023 10:56', N'APPR', 100012)
    ,   (N'WO00001', N'21/3/2023 10:58', N'WAPPR', 100014)
    ,   (N'WO00002', N'22/3/2023 11:03', N'APPR', 100007)
    ,   (N'WO00002', N'24/3/2023 10:56', N'COMP', 100009)
    ,   (N'WO00003', N'21/3/2023 10:59', N'WAPPR', 100016)
    ,   (N'WO00003', N'21/3/2023 11:01', N'APPR', 100017)
    ,   (N'WO00003', N'24/3/2023 10:56', N'WSCH', 100018)
) t (wonum,changedate,wostatus,wostatusid)
*/