Filtering out double records by cleanup/setup DATETIMES

[Anonymous]

I have 2 Tables

Card:

CARD_ID

CLEANUP_DT

SETUP_DT

SELECTED_CARD:

CASE_CONFIRMATION_ID

CARD_ID

A Case Confirmation_ID can have more then one Card associated with it. I however only want the Cleanup or the Setup time that is higher. Both of witch are DATETIME variables that show up as 1/1/1901 12:15:00 AM for 15 Minutes or 1/1/1901 12:30:00 AM for 30 Minute setup/cleanup times.

Case Confirmation ID	Card ID	SETUP DT	CLEANUP DT
135986	236587	1/1/1901 12:15:00 AM	1/1/1901 12:15:00 AM
135986	236526	1/1/1901 12:30:00 AM	1/1/1901 12:15:00 AM
189763	135498	1/1/1901 12:15:00 AM	1/1/1901 12:15:00 AM
169873	165798	1/1/1901 12:15:00 AM	1/1/1901 12:15:00 AM

For case 135986 it would just pick out CARD_ID 236526 and ignore 236587.

[aarkay29]

SELECTED_CARD:

CASE_CONFIRMATION_ID,

CARD_ID

From

     SELECTED_CARD;

Left Join (SELECTED_CARD)

Card:

CARD_ID,

CLEANUP_DT

SETUP_DT

From

     Card;

Inner Join(SELECTED_CARD)

Load

CASE_CONFIRMATION_ID,

Max(SETUP_DT) as SETUP_DT ,

Resident

     SELECTED_CARD

Group By

     CASE_CONFIRMATION_ID;