• Adi Jaradat

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.


Links

https://adijaradat.com/post/work-order-closure/

https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2005.htm#i1583352
15 views0 comments

Recent Posts

See All

Maximo Where Clause

Where clauses are used in Maximo intensively, such as in relationships and search, with a few tricks where clauses can do more than just sea

Search All Maximo Tables and All Columns

There are many way to implement Google-like search facility for any system such as Maximo. This is a powerful query to search all tables and