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.
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)
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)
1. All locations which have no equipment
LocationId NOT IN (SELECT LocationId FROM Equipment WHERE Equipment.LocationId = vbLocation.LocationId)
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)