0

Possible Duplicate:
SQL Query with ODBC, Joins and Group Bys

I am getting duplicate customer records with the query below. The main purpose of the query is to pull a list of customers into an Access db from ODBC (our customers are stored in DB2 on IBM i). The query is intended to look for a customer (OM01U1.OM01015) that has not had a delivery (RS2101F.DELDATE) or service (EC01_LASMTCDAT) in over 13 months but is not already suppressed (OM01U1.OM01068). I have narrowed it down to the EC01 records (Equipment) that are causing the duplicates. The query is giving results with the last maintenance record for every piece of equipment (EC01_EQUNUM, EC01_EQUACY, and EC01_EQUSPP) because some customers have several pieces of equipment that could have been maintained at different dates. I just need the most recent maintenance date (EC01_LASMTCDAT) for the outlet (OM01U1.OM01015). Is there a statement I can use to only pull the outlet's last maintenance date (EC01_LASMTCDAT)?

SELECT 
OM01U1.OM01041 as Loc, 
OM01U1.OM01015 AS Outlet, 
OM01U1.OM01945 AS OLTyp, 
OM01U1.OM01052 AS Outlet_Name, 
OM01U1.OM01054A AS Street_Number, 
OM01U1.OM01054C AS Street_Name, 
OM01U1.OM01055A AS City, 
OM01U1.OM01055B AS State, 
OM01U1.OM01106 AS Zip, 
OM01U1.OM01058A AS Area_Code, 
OM01U1.OM01058C AS Phone, 
OM01U1.OM01037 AS Channel, 
OM01U1.OM01926 AS USA_Type, 
OM01U1.OM01078 AS Key_Acct, 
OM01U1.OM01110 AS TRDGRP, 
OM01U1.OM01034 AS Trade_Name, 
OM01U1.OM01248 AS DTC, 
OM01U1.OM01073 AS Sales_Route, 
OM01U1.OM01068 AS Sup_CDE, 
OM01U1.OM01982 AS Sup_Reason, 
OM01U1.OM01067 AS Sup_Date, 
OM01U1.OM01065 AS Creation_DT, 
OM01U1.OM01066 AS Update_DT, 
OM01U1.OM01141 AS CAN_NUM, 
OC02_RNTCTCSTA AS OC_CONT_STATUS, 
CF30_CNSTYP AS CF_CONTYP, 
EC01_SYSSTA AS EC_STATUS, 
RS2101F.DELDATE AS RS_LAST_DEL_DT, 
EC01_LASMTCDAT AS EC_LAST_MNT_DT, 
COUNT (EC01_EQUNUM) AS EC_EQUIP, 
COUNT (EC01_EQUACY) AS EC_ACCESSORY, 
COUNT (EC01_EQUSPP) AS EC_COMPONENT
FROM OM01U1
JOIN RS2101F ON OM01U1.OM01015 = RS2101F.OUTNUM 
LEFT JOIN CF30 ON OM01U1.OM01015 = CF30_OUTNUM 
LEFT JOIN EC01 ON OM01U1.OM01015 = EC01_EQUOUTNUM 
LEFT JOIN OC02 ON OM01U1.OM01015 = OC02_OUTNUM
GROUP BY EC01_LASMTCDAT,EC01_SYSSTA,EC01_EQUNUM,EC01_EQUSPP,EC01_EQUACY,
CF30_CNSTYP,OM01U1.OM01141,OM01U1.OM01066,OM01U1.OM01065,
OM01U1.OM01067,OM01U1.OM01982,OM01U1.OM01068,OM01U1.OM01073
,OM01U1.OM01248,OM01U1.OM01034,OM01U1.OM01110,OM01U1.OM01078,OM01U1.OM01926,
OM01U1.OM01037,OM01U1.OM01058C,OM01U1.OM01058A,OM01U1.OM01106,OM01U1.OM01055B,
OM01U1.OM01055A,OM01U1.OM01054C,OM01U1.OM01054A,OM01U1.OM01052,OM01U1.OM01945,
OM01U1.OM01015,OM01U1.OM01041,RS2101F.DELDATE,OC02_RNTCTCSTA

HAVING 
RS2101F.DELDATE < 1110815 
AND EC01_LASMTCDAT < 1110815 
AND OM01U1.OM01068<>'S' 

Browse other questions tagged or ask your own question.