Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

T-SQL Help Request for Simple Query

Posted on 2014-04-30
4
Medium Priority
?
208 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 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 48

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

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 part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

610 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