Filters

CEM allows arbitrarily complex filters. Below are some of the commonly requested filters, grouped by module.

A B C D E F G H I J K L M N O P Q R S T U V W

Equipment

1. Find all equipment with open jobs (excluding PPM's)

equipmentid in (select equipmentid from (job inner join  
jobtypeid on job.jobtypeid = jobtype.jobtypeid) inner join jobstatus on job.jobstatusid = jobstatus.jobstatusid where jobtypeclassid != 'WWW2000042800001' and 
jobstatusid != '')

2. Find all equipment which has been tested with a given tester since a given date

EquipmentId in (select Job.EquipmentId from (TestJob inner join Equipment as Tester on TestJob.EquipmentTesterID = Tester.EquipmentId) inner join Job on TestJob.JobId = Job.JobId where ltrim(rtrim(Tester.EquipmentNumber)) = 'CHANGETHIS' and TestDate > 'CHANGETHIS')

3. Find all equipment which is on a PPM schedule but which does not have an outstanding job for each schedule. This is a useful way of finding out if your PPM schedules are running correctly.

vequipmentsce.equipmentid not in ( select job.equipmentid from job inner join ppmequipment on ppmequipment.equipmentid = job.equipmentid inner join equipment on ppmequipment.equipmentid = equipment.equipmentid inner join jobstatus on job.jobstatusid = jobstatus.jobstatusid inner join jobtype on job.jobtypeid = jobtype.jobtypeid where jobstatusclassid = 'WWW2000042700001' and jobtypeclassid = 'WWW2000042800001' and ppmequipment.recordstatus = 0 and job.equipmentid = equipment.equipmentid and job.recordstatus = 0 ) and equipmentstatusname not in ('suspended', 'decommissioned')
and vequipmentsce.recordstatus = 0 and vequipmentsce.equipmentid in (select equipmentid from ppmequipment)

Jobs

 

 

Models

 

Parts

1. All parts on outstanding orders

productid in(select productid from ordering, orderline where ordering.orderid = orderline.orderid and (ordering.orderingstatusid is null or ordering.orderingstatusid != 'WWW2001081400000' ) and IsDelivered = 0 and OrderLine.recordstatus < 8) 

 

Personnel

1. All personnel who have been on a given training course

 

Related resources

• You might also find some filter definitions on the Leeds user group web site.
• For advanced users there is a comprehensive introduction to extended SQL filters in the document set.