T-SQL Help Request for Simple Query

Here is my table:

COL _1	COL_2	COL_3	COL_4
  1     ABC     XYZ     4/30/14
  1     ABC     XYZ     4/30/14
  1     ABC     ABC     4/30/14
  2     XYZ 	XYZ	4/29/12
  2    	XYZ	XYZ     4/29/12
  2     XYZ     XYZ  	4/30/14
  2     ABC     ABC     4/30/14

Open in new window

What I need is a T-SQL query that returns just the change in COL_2. For example, what I'd like to see is this result set:
Result Set:
COL _1	COL_2	COL_3	COL_4
  1     ABC     XYZ     4/30/14
  1     ABC     ABC     4/30/14
  2     ABC 	XYZ	4/29/12
  2     ABC     ABC     4/30/14

Open in new window

ABC and XYZ are status results.

COL_2 will always contain the last status.
COL_3 is the current status at the time of COL_4

What I want to track is only where we changed status from XYZ to ABC or vice versa.

All the JOINs I've tried end up exploding on me.

LVL 20
Daniel Van Der WerkenIndependent ConsultantAsked:
Who is Participating?
Dale FyeConnect With a Mentor Commented:
Don't know why you think you need a join.

SELECT Col_1, Col_2, Col_3, Col_4
GROUP BY Col_1, Col_2, Col_3, Col_4

However, if Col_4 actually contains a Date/Time value rather than just a date, then this would basically give you your original dataset.

Also, is there a chance that you would change from

ABC to XYZ to ABC to XYZ all in the same day?  If so, and if Col_4 only contains a date, what would you use to define what the most current state would be?
FROM dbo.Table
Daniel Van Der WerkenIndependent ConsultantAuthor Commented:
Yeah. I didn't think I needed a JOIN either. I just don't GROK aggregates inherently. I knew it was simple but I was having difficulty getting past my mental roadblock on this one.

The dates are not the same and neither are the times. So, it's not going to be as straight-forward as I want, but you got me in the right direction.

Anything can change on the same day. I could go from XYZ to ABC and back to XYZ on the same day, even.

The actual tables have far more columns available as well as DATETIME values and so forth. The primary thing I need to do is capture status changes, and who did the change and ideally when. The problem is, that the change table doesn't just show status changes, but instead it shows all changes. Some might be status changes.
Dale FyeCommented:
Glad I could help.

I suspect that with the more complicated table structure and data you describe, that you will actually need some form of subquery to identify when the status changes occurred.  But we would need more sample data to figure that out.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.