CMMS Tech Tip: Leverage Intelligent Date Queries for Work Order Data

 

Challenge: Accessing work order and other maintenance management data

Mainsaver’s intelligent date queries provide an efficient way to obtain work order data based on the current date. Whether you’re tracking open work orders, preventive maintenance (PM) schedules, or other aspects of your maintenance operations, leveraging these queries can significantly improve your maintenance management. This CMMS Tech Tip delves into the use of intelligent date queries with a primary focus on work order data retrieval.

Methodology:  Utilizing ‘getdate()’ Function

The key to harnessing intelligent date queries lies in the ‘getdate()’ function, a powerful tool in SQL Server that retrieves the current date and time from the workstation. This function serves as the foundation for constructing date-based queries that can be customized to suit various scenarios. By manipulating the results of ‘getdate()’, you can access data that is relevant to your maintenance needs. Note: These queries will work for customers using the SQL Server database.  The Oracle parameter is slightly different.
 
  • The queries all use a SQL Server function called ‘getdate()’ which will obtain the current date and time from the workstation.
  • Days may be added or subtracted from ‘getdate()’ to compute a date in the future or in the past.
  • All these queries may be saved.
Example Queries with ‘getdate()’:
 
Work orders originated in past 7 days.

This query provides a snapshot of work orders that were initiated in the last week. By subtracting 7 days from ‘getdate()’, you can filter out work orders with an origination date within this timeframe.
work orders originated in the past 7 days

2. Work Orders Entered into the System in the Past 30 Days 

In this case, the focus shifts to when work orders were entered into the system rather than their origination date. By subtracting 30 days from ‘getdate()’, you can access work orders entered within the past month.

Find work orders entered in the past 30 days

3. PM Master Records Due Within the Next 30 Days

Preventive maintenance is critical for avoiding costly breakdowns. This query allows you to identify PM tasks that are coming due within the next 30 days, enabling proactive planning and scheduling.

Find PM tasks due in next 30 days

4. POs Created Today

To track purchase orders (POs) generated on the current day, you can use the ‘cast(getdate() as varchar(12))’ function to extract the date portion and filter for records where the date is equal to today.

Locate POs created today in your CMMS

5. POs Created with 1 Day Lead Time

If you need to monitor POs created a day in advance, you can manipulate ‘getdate()’ to display POs with a specific lead time.

Monitor POs created one day in advance

6. Parts with Issue or Receive Activity Within the Past 7 Days

For managing inventory and supplies, you can query for parts with issue or receive activity within the last week, helping you maintain optimal stock levels.

Query for parts with issue or receive activity in the past 7 days

7. User Tips: One-Time Queries vs. Saved Queries

While intelligent date queries provide real-time access to relevant data, it’s essential to understand the distinction between one-time queries and saved queries. A one-time query with the ‘today’ operator offers immediate results but cannot be saved for future use. In contrast, saved queries based on ‘getdate()’ can be reused to streamline your maintenance data retrieval process.

Save CMMS queries for future use

Summary: 

By effectively leveraging intelligent date queries within Mainsaver, organizations can enhance their maintenance operations, reduce downtime, and optimize resource allocation, all of which contribute to increased efficiency and cost savings.

Learn more about Mainsaver’s maintenance management solutions

Mainsaver Cloud

CMMS

Mainsaver Cloud is the new web-based SaaS version of our CMMS platform that’s easy to use and implement, accessible anywhere, and affordable for small and medium-sized businesses.

Mainsaver Core

EAM

Mainsaver Core offers comprehensive enterprise asset management capabilities in a customizable on-premise or web-based solution that meets the needs of the largest and most complex organizations.

Learn more about Mainsaver’s maintenance management solutions

Mainsaver Cloud

CMMS

Mainsaver Cloud is the new web-based SaaS version of our CMMS platform that’s easy to use and implement, accessible anywhere, and affordable for small and medium-sized businesses.

Mainsaver Core

EAM

Mainsaver Core offers comprehensive enterprise asset management capabilities in a customizable on-premise or web-based solution that meets the needs of the largest and most complex organizations.