e-Quip AM Filters

Most queries that users run on a day-to-day basis can be achieved very simply using any of the e-Quip AM built-in searching techniques. However, sometimes users will request complex filters which require knowledge of SQL (the query language used to interrogate the e-Quip database). If we feel that a filter might be of interest to others, then we will generally publish it here.

If you would like a particular filter or calculated field, then let us know.

The filters fields are grouped together by the summary screen that they relate to. To use an SQL filter, go to the appropriate summary screen and then click Filter Wizard on the ribbon strip. When the wizard appears, choose the SQL tab and then copy & paste the appropriate filter into the filter text box.

Assets

1. All assets with more than 10 jobs

EquipmentId IN (SELECT EquipmentId FROM Job WHERE vbAsset.EquipmentId = Job.EquipmentId GROUP BY Job.EquipmentId HAVING COUNT(*) >= 10)

Categories

1. All categories which have no models

CategoryId NOT IN (SELECT CategoryId FROM Model WHERE Model.CategoryId = vbCategory.CategoryId)

2. All categories which have no equipment

CategoryId NOT IN (SELECT CategoryId FROM Equipment WHERE Equipment.CategoryId = vbCategory.CategoryId)

Jobs

Locations

1. All locations which have no equipment

LocationId NOT IN (SELECT LocationId FROM Equipment WHERE Equipment.LocationId = vbLocation.LocationId)

Models

1. All models with more than 500 jobs

ModelId IN (SELECT ModelId FROM Job WHERE vbModel.ModelId = Job.ModelId GROUP BY Job.ModelId HAVING COUNT(*) >= 500)

2. All models which have no equipment

ModelId NOT IN (SELECT ModelId FROM Equipment WHERE Equipment.ModelId = vbModel.ModelId)