Lrobinson 93181 (8/6/2013)
I have provided the code and an example of the expected result set...so I am not sure what doesn't make sense? Maybe a step-by-step would help...it took me several conversations with the user to finally get it.1. Pull the records from each group with the latest c_dt
This should return these two records:
'11111','2010-09-06 12:00AM','25'
'11111','2010-10-06 12:00AM','23'
Where the C_Id is the same, but the c_rep is different.
This doesn't seem to make sense to me. By latest I assume you mean the most recent or MAX(c_dt)? In your sample data the max and min for both group are the same.
select c_id, min(c_dt) as MinDate, Max(c_dt) as MaxDate, c_rep
from #mytable
group by c_ID, c_rep
I can't even begin to think about the second part because I don't understand your first part. 🙂
_______________________________________________________________
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/