Hi, I hope this is what you wanted to see:

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable

(

c_ID INT IDENTITY(10,1) PRIMARY KEY CLUSTERED,

c_dt DATETIME,

last_dt DATETIME,

c_rep INT,

)

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table

INSERT INTO #mytable

(c_iD, c_dt, last_dt, c_rep )

SELECT '11111','2010-09-06 12:00AM','2010-09-04 12:00AM','25' UNION ALL

SELECT '11111','2010-09-06 12:00AM','2010-08-04 12:00AM','25' UNION ALL

SELECT '11111','2010-09-06 12:00AM','2010-07-04 12:00AM','23' UNION ALL

SELECT '11111','2010-09-06 12:00AM','2010-06-04 12:00AM','25' UNION ALL

SELECT '11111','2010-09-06 12:00AM','2010-05-04 12:00AM','23' UNION ALL

SELECT '11111','2010-10-06 12:00AM','2010-10-04 12:00AM','25' UNION ALL

SELECT '11111','2010-10-06 12:00AM','2010-09-04 12:00AM','25' UNION ALL

SELECT '11111','2010-10-06 12:00AM','2010-08-04 12:00AM','25' UNION ALL

SELECT '11111','2010-10-06 12:00AM','2010-07-04 12:00AM','23'

--===== Set the identity insert back to normal

SET IDENTITY_INSERT #mytable OFF

First, I need to pull the c_id, c_dt, and s_code for specific s_codes by the last c_dt for those specific codes. Once I have those, I want to pull the matching c_ids for any transactions that occured prior to each distinct c_dt in in my initial query, and select only the last c_id date from that group of records, so that my result is:

'11111','2010-09-06 12:00AM','2010-09-04 12:00AM','25'

'11111','2010-10-06 12:00AM','2010-10-04 12:00AM','25'

Many thanks!