Constructing a Calendar Query
Updated: Jun 21
If you are working with Maximo you must have realized the importance of calendars for numerous reasons. If you were challenged to analyze Work Orders, resources allocation, leveling or long term planning you will probably need a calendar “data set”. The following query helps constructing a dynamic calendar starting from SYSDATE plus 12 months.
Of course, sliding this query into a recursive query will help you reference a solid data set of dates which can be joined with Maximo Calendars if you are using them. We will use Oracles ALL_OBJECTS view which describes objects accessible to current user (the user context in which the query is running).
SELECT TRUNC(SYSDATE,'y')-1+ROWNUM DT ,TO_CHAR(TRUNC(SYSDATE,'y')-1+ROWNUM,'DAY') WEEKDAY FROM ALL_OBJECTS WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),(2016-TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))+1)*12) - TRUNC(SYSDATE,'y')
Using this dynamic in combination with Work Order dates will certainly allow you to plot all those Work Orders and establish a solid KPI.