Solved

T-SQL Help Request for Simple Query

Posted on 2014-04-30
4
200 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 47

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 47

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now