I have inherited a query which currently returns multiple instances of each work order because of the joined tables. The code is here and I've detailed the criteria needed below but need some help on the best way to accomplish this:Select h.worknumber, h.itemcode, h.descr, h.task_descr, h.qty, h.itemised, h.serialnum, h.manufacturer, h.model_id, h.depot, h.date_in, h.date_approved, h.est_complete_date, h.actual_complete_date, h.meterstart, h.meterstop, h.custnum, h.name cust_name, h.addr1, h.addr2, h.town, h.county, h.postcode, h.country_id, h.contact, h.sitename, h.siteaddr1, h.siteaddr2, h.sitetown, h.sitecounty, h.sitepostcode, h.sitecountry_id, h.phonenum, h.faxnum, h.on_site, h.charge_to_cust, h.charge_to_contract, h.cust_ponum, h.estimated_by, h.approved_by, h.invnotes, h.warranty_repair, h.stage, h.est_travel_time, h.time_on_site, h.time_off_site, h.item_owned_by_cust, t.name tech_name, ty.name worktype_name, c.name depot_name, m.name model_name, i.meter, i.meter_date, sm.next_calendar_date, Dateadd(year, 2,iv.purchase_date) as [Warranty Expiry], wh.meterstop as [Last Service Hours], wh.date_created as [Last Service]From vwWorksorderHeader h Left Outer Join workstechnicians t On t.id = h.technician_id Left Outer Join worksordertypes ty On ty.id = h.worktype Left Outer Join company c On c.office = h.depot Left Outer Join models m On m.id = h.model_id left outer join inventory iv on iv.item = h.itemcode left outer join inventory i on i.mfg = h.itemcode inner join sched_maintenance sm on sm.code = h.itemcode left outer join worksorderhdr wh on wh.itemcode = h.itemcode where wh.worktype in (11,12,13,14,15,16,17,18,19,20,21,22,32) and wh.rejected = 0 and sm.description like 'CERT%'
Each work order should only be returned once, and with the following additional criteria:1. i.meter
- this should return only the lowest number from that file.2. sm.next_calendar_date
- this should return only the most recent date out of those selected for the certificates on this piece of equipment3. wh.meterstop as [Last Service Hours], wh.date_created as [Last Service]
- this should return the number from wh.meterstop
at the most recent wh.date_created
for that piece of equipment.I know this is a tricky one and it might take more than one pass but would appreciate some help as I'm not sure how to achieve what is wanted.Many thanksMartyn 
Selecting only 1 record based on multiple criteria
Posted - 2014-01-31 : 08:11:06
