Solved

T-SQL Help Request for Simple Query

Posted on 2014-04-30
4
203 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 9

Expert Comment

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

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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 48

Expert Comment

by:Dale Fye (Access MVP)
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

717 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