NEWBY question - filtering dates

Im not sure how to explain this or if this is the correct forum but here it goes.

I have created a report from a view that gives me court hearing dates on a defendent. When I run the report/view I get two different dates for the person such as, 12/14/2006 and 12/15/2006. How can I get it to show only the later date (12/15/2006)? I pull information from a mainframe that almost always gives me 2 different dates, but we only need the most recent date.

Here is my query for the view

SELECT dbo.CASES.CaseID, dbo.CASENUMBERS.CaseNumber AS Arrest_Number, dbo.CASENUMBERTYPES.CaseNumTypeCode AS Arrest_NumType,
CASENUMBERS_1.CaseNumber AS JN_Number, CASENUMBERTYPES_1.CaseNumTypeCode AS JN_NumType, dbo.ROLETYPES.RoleTypeDesc,
dbo.ACTORS.SIDNum, dbo.ACTORLOCATIONS.ActorLocDesc, dbo.CHARGENUMBER.ChargeDescription, dbo.DEFCHARGES.OffenseDate,
dbo.fnNameReverse(dbo.ACTORNAMES.LastName, dbo.ACTORNAMES.FirstName, dbo.ACTORNAMES.MiddleName, dbo.ACTORNAMES.Suffix)
AS NameRev, dbo.EVENTS.ev_StartDate AS PreHearingSetDate, dbo.OFFENSELEVELS.OffenseLevelDesc, dbo.CASES.UnitID
FROM dbo.CASES INNER JOIN
dbo.CASENUMBERS ON dbo.CASES.CaseID = dbo.CASENUMBERS.CaseID INNER JOIN
dbo.CASENUMBERTYPES ON dbo.CASENUMBERS.CaseNumTypeID = dbo.CASENUMBERTYPES.CaseNumTypeID INNER JOIN
dbo.CASENUMBERS AS CASENUMBERS_1 ON dbo.CASES.CaseID = CASENUMBERS_1.CaseID INNER JOIN
dbo.CASENUMBERTYPES AS CASENUMBERTYPES_1 ON CASENUMBERS_1.CaseNumTypeID = CASENUMBERTYPES_1.CaseNumTypeID INNER JOIN
dbo.CASEPARTIES ON dbo.CASES.CaseID = dbo.CASEPARTIES.CaseID INNER JOIN
dbo.ROLETYPES ON dbo.CASEPARTIES.RoleTypeID = dbo.ROLETYPES.RoleTypeID AND
dbo.CASEPARTIES.RoleTypeID = dbo.ROLETYPES.RoleTypeID INNER JOIN
dbo.ACTORNAMES ON dbo.CASEPARTIES.ActorNameID = dbo.ACTORNAMES.ActorNameID INNER JOIN
dbo.ACTORS ON dbo.ACTORNAMES.ActorID = dbo.ACTORS.ActorID INNER JOIN
dbo.ACTORLOCATIONS ON dbo.ACTORS.ActorLocationID = dbo.ACTORLOCATIONS.ActorLocationID INNER JOIN
dbo.DEFCHARGESUMMARY ON dbo.CASES.CaseID = dbo.DEFCHARGESUMMARY.CasesCaseID INNER JOIN
dbo.DEFCHARGES ON dbo.DEFCHARGESUMMARY.DefChargeID = dbo.DEFCHARGES.DefChargeID INNER JOIN
dbo.CHARGENUMBER ON dbo.DEFCHARGES.ChargeNumberID = dbo.CHARGENUMBER.ChargeNumberID INNER JOIN
dbo.COURTS ON dbo.CASES.CaseCourtID = dbo.COURTS.CourtID INNER JOIN
dbo.EVENTS ON dbo.CASES.CaseID = dbo.EVENTS.CaseID INNER JOIN
dbo.EVENTTYPES ON dbo.EVENTS.EventTypeID = dbo.EVENTTYPES.EventTypeID INNER JOIN
dbo.OFFENSELEVELS ON dbo.CHARGENUMBER.OffenseLevelID = dbo.OFFENSELEVELS.OffenseLevelID
WHERE (dbo.CASENUMBERTYPES.CaseNumTypeCode = 'NM' OR
dbo.CASENUMBERTYPES.CaseNumTypeCode = 'MC' OR
dbo.CASENUMBERTYPES.CaseNumTypeCode = 'CM' OR
dbo.CASENUMBERTYPES.CaseNumTypeCode = 'JP') AND (CASENUMBERTYPES_1.CaseNumTypeCode = 'JN') AND
(dbo.ROLETYPES.RoleTypeDesc = 'Defendant') AND (dbo.EVENTTYPES.EventTypeDesc = 'PRE-HEARING SET') AND
(dbo.DEFCHARGES.OffenseDate > CONVERT(DATETIME, '2006-01-01 00:00:00', 102))

In the 'PRE-HEARING SET' table is where I receive the two dates. Is there a way to filter out the first date? I just want to show the most recent date.

Hi,

as you do not SELECT anything from the events table what about:

--dbo.EVENTTYPES ON dbo.EVENTS.EventTypeID = dbo.EVENTTYPES.EventTypeID INNER JOIN

dbo.OFFENSELEVELS ON dbo.CHARGENUMBER.OffenseLevelID = dbo.OFFENSELEVELS.OffenseLevelID

WHERE (dbo.CASENUMBERTYPES.CaseNumTypeCode = 'NM' OR

dbo.CASENUMBERTYPES.CaseNumTypeCode = 'MC' OR

dbo.CASENUMBERTYPES.CaseNumTypeCode = 'CM' OR

dbo.CASENUMBERTYPES.CaseNumTypeCode = 'JP') AND (CASENUMBERTYPES_1.CaseNumTypeCode = 'JN') AND

(dbo.ROLETYPES.RoleTypeDesc = 'Defendant') AND

(dbo.DEFCHARGES.OffenseDate > CONVERT(DATETIME, '2006-01-01 00:00:00', 102))

AND EXISTS(SELECT * FROM dbo.EVENTTYPES WHERE EVENTS.EventTypeID = dbo.EVENTTYPES.EventTypeID AND dbo.EVENTTYPES.EventTypeDesc = 'PRE-HEARING SET')

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de