Kind of a complicated question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Kind of a complicated question

Author

Topic

Japboix1
Starting Member

24 Posts

Posted - 2011-07-08 : 15:02:12

Hello all,
I need some help. Let's say that I have two columns filled with numbers. I want to know when the entry in either columns changes. Basically, I need to know when one column contains a unique value adn the other doesn't. Is there anyway to do this?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-08 : 15:11:44

quote:
Originally posted by Japboix1

Hello all,
I need some help. Let's say that I have two columns filled with numbers. I want to know when the entry in either columns changes. Basically, I need to know when one column contains a unique value adn the other doesn't. Is there anyway to do this?

Aren't those two different questions?

If you want to keep track of changes to the data in the table, you can do it using a trigger that would write the changes to an audit table.

If you are trying to see if the values in the two columns are not the same, you can do it using

select * from YourTable where column1 <> column2

Japboix1
Starting Member

24 Posts

Posted - 2011-07-08 : 15:21:58

No, I know that the information in both columns is not the same. What I want to know is when one column changes and the other doesn't.

Like, if I had

____________
5 |6
5 |6
7 |6
8 |10

Is there a way to know when the value in the first column changed while the one in the second column stayed the same? Or what row it was when the value changed?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-08 : 15:31:41

If I understood you correctly, what you want to do pick up all rows that satisfy these two conditions:

1. value in the first column is the same as the value in the first column in the previous row

2. value in the second column is not the same as the value in the second column in the previous row.

You can write a query to do this, but one prerequisite is that you need a way to order the rows. By definition, the rows in a table are an unordered collection. So you have to find some way to order it. You might say, for example, order them by column1 and then column2. Or may be there are other columns in your table that will help you determine that.

If you decided that ordering by column1, column2 is the way to go, the query would be something like this:

;WITH cte AS 
(
   SELECT *, ROW_NUMBER() OVER (ORDER BY col1,col2) AS rn
   FROM YourTable
)
SELECT
   a.col1 AS currentCol1,
   a.col2 AS currentCol2,
   b.col1 AS PreviousCol1,
   b.col2 AS PreviousCol2
FROM
   cte a
   INNER JOIN cte b ON a.rn = b.rn+1
WHERE
   a.col1 = b1.col1 AND a.col2 <> b.col2 -- or other conditions as required.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-08 : 15:43:40

quote:
Originally posted by Japboix1

No, I know that the information in both columns is not the same. What I want to know is when one column changes and the other doesn't.

Like, if I had

____________
5 |6
5 |6
7 |6
8 |10

Is there a way to know when the value in the first column changed while the one in the second column stayed the same? Or what row it was when the value changed?

Are you talking about a change in the number from row to row? For example if you start at the top tuple 5,6 and work your way down to 8,10. At that point the 6 changed to a 10. Is that what you want to identify? Or are you talking about capturing a change to the value when you are doing an update? Say I update columnB to 9 where the values are 5 and 6.

Japboix1
Starting Member

24 Posts

Posted - 2011-07-08 : 16:06:43

Yeah, Sunitabeck got what I meant. Sorry if I was unclear about that. I'll try what you suggested then.

HaYen
Starting Member

6 Posts

Posted - 2011-07-09 : 02:25:00

You still want an UPDATE Trigger. So say you have 1 row with 2 columns that currently have the values 11 and 22. The Trigger is going to allow you to inspect the current values and compare them to the values coming in. The values coming in are referenced as inserted and the older values are referenced as deleted. So if the requirement is know when one values has changed but the other hasn't, try this for core logic:

CREATE TRIGGER EmailMe
ON MyTable
FOR UPDATE
AS

IF UPDATE (col1) AND UPDATE (col2)
PRINT 'DO NOTHING'
ELSE
EXEC master..xp_sendmail 'JonSmith',
'Row ' + CAST(inserted.ROWIDFIELD AS CHAR(10)) + ' had a mismatch change.'
GO

Now in the code above, will have to have a minimum of three columns (col1, col2, and ROWIDFIELD). The UPDATE TRIGGER will fire whenever a change is made to a row. The TRIGGER will inspect col1 and col2 values to see if they were updated. If they both were updated, you don't have to do anything. If one was changed and the other wasn't, you could send an email or raise an error. I don't know if this update will come by way of an interactive process (WIN/WEB app) or batch app. You may want to ad a second condition after the first to say ELSE IF NOT UPDATED(col1) AND NOT UPDATED(col2). In that case you still don't want to do anything. And I don't have my SQL box up right now so I can test the NOT scenarios, but it should work.

The only other problem is if there are multiple rows updated. If there is a chance more than one row can be updated at a time, I've loaded the results of the INSERTED values and the DELETED along with a ROW_NUMBER values into a temp table/table variable.

SELECT INSERTED.COL1 AS col1I, INSERTED.COL2 AS COL2I, DELETED.COL1 AS COL1D, DELETED.COL2 AS COL2D, SOME_ROW_ID_COLUMN, ROW_NUMBER() OVER(ORDER BY col1) AS RowNumber, 'same' AS ChangeFlag
INTO #MyTempTable

Assigned a row count to a variable:
DECLARE @ROWCOUNT AS INTEGER
SET @ROWCOUNT = (SELECT COUNT(*) FROM #MyTempTable)

DECLARE @MyIterator AS INTEGER = 1;
DECLARE @col1Inserted AS INTEGER;
DECLARE @col1Deleted AS INTEGER;
DECLARE @col2Inserted AS INTEGER;
DECLARE @Col2Deleted AS INTEGER;

WHILE @MyIterator <= @ROWCOUNT
BEGIN
SET @col1Inserted = (SELECT COL1I FROM #MyTempTable WHERE RowNumber = @MyIterator)
SET @col2Inserted = (SELECT COL2I FROM #MyTempTable WHERE RowNumber = @MyIterator)
SET @col1Deleted = (SELECT COL1D FROM #MyTempTable WHERE RowNumber = @MyIterator)
SET @col2Deleted = (SELECT COL2D FROM #MyTempTable WHERE RowNumber = @MyIterator)

IF (@col1Inserted <> @col1Delete AND @col2Inserted = @col2Deleted) OR
(@col1Inserted = @col1Delete AND @col2Inserted <> @col2Deleted)
UPDATE #MyTempTable SET ChangeFlag = 'Different' WHERE RowNumber = @MyIterator
END

If (SELECT COUNT(*) FROM #MyTempTable WHERE ChangeFlag = 'Different') > 0
EXEC master..xp_sendmail 'JonSmith',
'The Following Rows had a mismatch change. ' + SELECT SOME_ROW_ID_COLUMN FROM #MyTempTable WHERE ChangeFlag = 'Different';

/*
Make sure the column you return (SOME_ROW_ID_COLUMN) is of type string or you'll have to cast the value to generate an email
Also you don't have to generate an email, you could raise an error:

RAISERROR('There were multiple updates where one column was changed and the other was not' ,16,10) with NOWAIT;

In that case I would make the #MyTempTable a permanent table.

Got to go too sleepy

HaYen

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-10 : 15:58:57

quote:
Originally posted by Japboix1

No, I know that the information in both columns is not the same. What I want to know is when one column changes and the other doesn't.

Like, if I had

____________
5 |6
5 |6
7 |6
8 |10

Is there a way to know when the value in the first column changed while the one in the second column stayed the same? Or what row it was when the value changed?

Have you ever read an intro book on RDBMS? Tables have no ordering, so this makes no sense at all. Change in plies time; where is teh temporal attribute?

What you posted is not even a table; it cannot ever have a key based on the data you gave us. Oh, it is also considered rude not post DDL in any SQL forum or newsgroup when asking questions.

Want to try again with valid DDL and a clear specification? But read that book first, please.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Japboix1
Starting Member

24 Posts

Posted - 2011-07-11 : 08:27:41

quote:
Originally posted by jcelko

quote:
Originally posted by Japboix1

No, I know that the information in both columns is not the same. What I want to know is when one column changes and the other doesn't.

Like, if I had

____________
5 |6
5 |6
7 |6
8 |10

Is there a way to know when the value in the first column changed while the one in the second column stayed the same? Or what row it was when the value changed?

Have you ever read an intro book on RDBMS? Tables have no ordering, so this makes no sense at all. Change in plies time; where is teh temporal attribute?

What you posted is not even a table; it cannot ever have a key based on the data you gave us. Oh, it is also considered rude not post DDL in any SQL forum or newsgroup when asking questions.

Want to try again with valid DDL and a clear specification? But read that book first, please.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Yes I do know that what I posted is not a correct table. I just did not know how to explain it without some sort of visual aid. It's just an example.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources