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.
1条答案
按热度按时间b5buobof1#
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:
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: