Your inserts didn't work because you have specified a value for each row but the values are all the same. I modified that so it will work.
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,
)
--===== Insert the test data into the test table
INSERT INTO #mytable
(c_dt, last_dt, c_rep )
SELECT '2010-09-06 12:00AM','2010-09-04 12:00AM','25' UNION ALL
SELECT '2010-09-06 12:00AM','2010-08-04 12:00AM','25' UNION ALL
SELECT '2010-09-06 12:00AM','2010-07-04 12:00AM','23' UNION ALL
SELECT '2010-09-06 12:00AM','2010-06-04 12:00AM','25' UNION ALL
SELECT '2010-09-06 12:00AM','2010-05-04 12:00AM','23' UNION ALL
SELECT '2010-10-06 12:00AM','2010-10-04 12:00AM','25' UNION ALL
SELECT '2010-10-06 12:00AM','2010-09-04 12:00AM','25' UNION ALL
SELECT '2010-10-06 12:00AM','2010-08-04 12:00AM','25' UNION ALL
SELECT '2010-10-06 12:00AM','2010-07-04 12:00AM','23'
select * from #mytable
Now for the actual problem.
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!
From that description I have absolutely no idea what the logic is supposed to be here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/