Display only changed values - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Display only changed values

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-10-15 : 10:35:08

hello,
I have a table like this :

Name Salary
Jamie 10000
Bob 20000
Steve 15000
Jamie 15000
Bob 20000
Steve 15000
Jane 50000
Jane 51000

and so on.

how can I do a count of all the people whos salaries that have changed ?
in this example the count will be 2 ( only Jamie and Jane's salary has changed)

any ideas ?

thank you.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-10-15 : 10:38:16

Here is a simple solution:

SELECT	COUNT(*)
FROM	(
	SELECT	NAME
	FROM	yourtable
	GROUP BY NAME
	HAVING COUNT(DISTINCT Salary) > 1
	) x

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-10-15 : 10:44:29

cheers for this, but I don't know if that is working correctly.
I'm expecting to only display a couple of hundred records. instead I am displaying a couple of 10,000's !.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-10-15 : 10:49:55

The COUNT(*) should display 1 record. The count itself.

Please clarify, because based on the table given above the query will work.

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-10-15 : 10:58:19

the table is not exactly like that, its more like :
ID Name Salary InsertDate
1 Jamie 10000 2003-01-01
2 Bob 20000 2003-01-01
3 Steve 15000 2003-10-02
4 Jamie 10000 2003-10-02
5 Jamie 10000 2003-10-03
6 Jamie 15000 2003-11-04

similar to above but with about 1 million records in.

ps. I am not getting a count of 1, but a total number of 13 thousand.
?

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-10-15 : 11:16:15

Just looking at the fields and Salary has a data tpye of varchar, would this mess things up ?

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-10-15 : 11:21:12

Run this in QA:

SELECT	a.*
FROM	yourtable a JOIN
	(
	SELECT	NAME
	FROM	yourtable
	GROUP BY NAME
	HAVING COUNT(DISTINCT Salary) > 1
	) x
ON
	a.Name = x.Name

ORDER BY Name, ID

Then scroll through the data and see if what you find is what you would expect. Note that we are bringing back ALL the details for names with salary changes so you will have a listing like this:

ID 	Name 	Salary 	InsertDate
1 	Jamie 	10000 	2003-01-01
4 	Jamie 	10000 	2003-10-02
5 	Jamie 	10000 	2003-10-03
6 	Jamie 	15000 	2003-11-04

look especially for variations of spellings for names that could be the same person.

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-10-15 : 11:30:20

yeah, that works, displaying all records in order.

are these all people with mopre than one salary ?
what next ?

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-10-15 : 14:14:26

yes varchar can, especially if user entered. Leading or trailing spaces would evaluate a different even if the value was the same.

"15000" <> " 15000" <> "15000 "

lets try this and see what happens.

For counts:

SELECT	COUNT(*)
FROM	(
	SELECT	NAME
	FROM	yourtable
	GROUP BY NAME
	HAVING COUNT(DISTINCT LTRIM(RTRIM(Salary))) > 1
	) x

For List:

SELECT	a.*
FROM	yourtable a JOIN
	(
	SELECT	NAME
	FROM	yourtable
	GROUP BY NAME
	HAVING COUNT(DISTINCT LTRIM(RTRIM(Salary))) > 1
	) x
ON
	a.Name = x.Name

ORDER BY Name, ID

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-10-16 : 07:36:31

nice one, that seemed to do the trick !
thank you for all your help.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-10-16 : 08:44:19

Excellent, thanks for the feedback.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources