I have the query shown in the code window. THe resulting list has every completed service call we had from 01/01/2009 showing the rep who handlet it and what zip code the customer is in.
The list will show duplicates rep zip and dates but what i want is to show the last rep to work on that piece of equipment.
so if the result were
then i want the last record to show becuas the date is higer. hope this makes sense. This of course needs to be applied to all the records so i cant use TOp 1 or something like that.
SELECT distinct SERV_CALL.REPRESENTATIVE AS REP, CUSTOMR_SHIP_TO.CUS_POSTAL_CODE_1 AS ZIP, SERV_CALL.COMPLETE_DATE_SCA
FROM SERV_CALL INNER JOIN
SERIAL_EQUIP ON SERV_CALL.SERIAL = SERIAL_EQUIP.SERIAL INNER JOIN
CUSTOMR_SHIP_TO ON SERIAL_EQUIP.CUSTOMER_NUMBER = CUSTOMR_SHIP_TO.CUSTOMER_NUMBER
WHERE (SERV_CALL.COMPLETE_DATE_SCA > '01/01/2009') AND (CUSTOMR_SHIP_TO.CUS_POSTAL_CODE_1 <> '')
order by SERV_CALL.COMPLETE_DATE_SCA DESC