Intelligent Date Queries

Challenge:

Mainsaver Queries allow for users to quickly display data in Mainsaver based on any of the columns in each module.  For example, ‘All Open Work Orders’ is a very common query that can be based on the work order status or other columns to display open work orders.  This tutorial will focus on queries that utilize the current date to obtain data in any module.

Note: These queries will only work for customers using the SQL Server database.  The Oracle parameter is slightly different.

Methodology:

  1. The queries all use a SQL Server function called ‘getdate()’ which will obtain the current date and time from the workstation.
  2. Days may be added or subtracted from ‘getdate()’ to compute a date in the future or in the past.
  3. Example Queries with ‘getdate()’.  All these queries may be saved.

Work orders originated in past 7 days.

Work orders entered into the system in the past 30 days.  Different from the origination date.

PM Master records due within the next 30 days.

POs created today. This query uses the term ‘cast(getdate() as varchar(12))’ to extract only the date in order to use the operator ‘=’.

PO’s created with 1 day.

Parts with issue or receive activity within the past 7 days.

  • The user may create a one-time query using the ‘today’ operator however this will not work in a saved query.

Parts with issue or receive activity           today.

To view more Tech Tips…