?
Solved

T-SQL Help Request for Simple Query

Posted on 2014-04-30
4
Medium Priority
?
209 Views
Last Modified: 2014-04-30
Here is my table:

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.

Thanks.
0
Comment
Question by:Daniel Van Der Werken
  • 2
4 Comments
 
LVL 9

Expert Comment

by:xav056
ID: 40032540
SELECT *
FROM dbo.Table
WHERE COL2<>COL3
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 40032551
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?
0
 
LVL 20

Author Closing Comment

by:Daniel Van Der Werken
ID: 40032847
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.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40032926
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.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question