Haven't yet had an opportunity to look deeply at this forum but -- I'll fire away with a newbie question....

 

I'm using Crystal XI to create a summary report.  Presently, too many detail records are retrieved.  The query that returns these records follows, it was generated using the

I have what is to me a complex query that returns appropriate detail records -- matter of fact, too many.... here is the automatically generated using the report->record selection editor.

 

 SELECT DISTINCT CallLog.CallID, CallLog.CallType, CallLog.RecvdDate, CallLog.CustID, Asgnmnt.Assignee, CallLog.Priority, CallLog.Category, Asgnmnt.Resolution, CallLog.RecvdTime, Asgnmnt.DateAcknow, Asgnmnt.TimeAcknow, Asgnmnt.WhoAcknow, Subset.RHC
 FROM   (heat.heat.CallLog CallLog LEFT OUTER JOIN heat.heat.Asgnmnt Asgnmnt ON CallLog.CallID=Asgnmnt.CallID) LEFT OUTER JOIN heat.heat.Subset Subset ON CallLog.CallID=Subset.CallID
 WHERE  CallLog.Category LIKE 'break%' AND Subset.RHC LIKE 'LMC%' AND CallLog.Priority='1' AND Asgnmnt.DateAcknow NOT  LIKE '' AND (Asgnmnt.Resolution='Completed' OR Asgnmnt.Resolution='Reassigned')
 ORDER BY CallLog.CallID

 

What I want to do is from the result set, is further reduce it by selecting just the minimum value for the combined elements of Asgnmnt.DateAcknow and Asgnmnt.TimeAcknow.  

 

In this case, there are multiple rows returned for a given CallLog.CallID.  Each row contains the values for asgnmnt.dateacknow and asgnmnt.timeacknow.   I'm only after the rows where the combined values previously mentioned are the minimum or min for the CallLog.CallID. I'm sure this is an easy one to solve but for the life of me, I can't figure it out.  THe data base is SQL 2000 or above...

Can anyone HELP!!!!

Thank You very much

Mark

THanks again...

Mark

 

-Dell