Query to show only when a value changes

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 2005 Forums

Transact-SQL (2005)

Query to show only when a value changes

Author

Topic

evesnight
Starting Member

10 Posts

Posted - 2010-06-30 : 17:31:14

I thought I had this one, I really did. Then it didn't work...

Say you have a table and data such as the following:

CREATE TABLE Vals (
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
Mth DATETIME NOT NULL,
Val DECIMAL(18,2) NOT NULL
)

INSERT INTO Vals (Mth, Val) values ('1/1/2000', 500)
INSERT INTO Vals (Mth, Val) values ('2/1/2000', 500)
INSERT INTO Vals (Mth, Val) values ('3/1/2000', 500)
INSERT INTO Vals (Mth, Val) values ('4/1/2000', 500)
INSERT INTO Vals (Mth, Val) values ('5/1/2000', 600)
INSERT INTO Vals (Mth, Val) values ('6/1/2000', 600)
INSERT INTO Vals (Mth, Val) values ('7/1/2000', 600)
INSERT INTO Vals (Mth, Val) values ('8/1/2000', 600)
INSERT INTO Vals (Mth, Val) values ('9/1/2000', 700)
INSERT INTO Vals (Mth, Val) values ('10/1/2000', 700)
INSERT INTO Vals (Mth, Val) values ('11/1/2000', 700)
INSERT INTO Vals (Mth, Val) values ('12/1/2000', 700)
INSERT INTO Vals (Mth, Val) values ('1/1/2001', 600)
INSERT INTO Vals (Mth, Val) values ('2/1/2001', 600)
INSERT INTO Vals (Mth, Val) values ('3/1/2001', 600)
INSERT INTO Vals (Mth, Val) values ('4/1/2001', 600)
INSERT INTO Vals (Mth, Val) values ('5/1/2001', 500)
INSERT INTO Vals (Mth, Val) values ('6/1/2001', 500)
INSERT INTO Vals (Mth, Val) values ('7/1/2001', 500)
INSERT INTO Vals (Mth, Val) values ('8/1/2001', 400)
INSERT INTO Vals (Mth, Val) values ('9/1/2001', 400)
INSERT INTO Vals (Mth, Val) values ('10/1/2001', 400)
INSERT INTO Vals (Mth, Val) values ('11/1/2001', 400)
INSERT INTO Vals (Mth, Val) values ('12/1/2001', 400)

Now write a query that pulls the Mth and Val fields ONLY when the Val changes. The result set should look like:

01/01/00, 500
05/01/00, 600
09/01/00, 700
01/01/01, 600
05/01/01, 500
08/01/01, 400

I had something that looked something like this but it doesn't work if the values are the same at a future date:

SELECT Mth, Val FROM (
SELECT MIN(Mth) AS Mth, Val FROM Vals GROUP BY Val) as a
ORDER BY Mth

The above query won't return the change back from 700 to 600.

Thoughts?

Thanks!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-06-30 : 18:21:04

SELECT * FROM (
SELECT Mth,
Val,
ROW_NUMBER() OVER(PARTITION BY YEAR(Mth),val ORDER BY val ) as BrazilWins FROM Vals
)
a WHERE BrazilWins = 1
ORDER BY Mth

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

evesnight
Starting Member

10 Posts

Posted - 2010-06-30 : 20:43:37

That almost works except that when there are multiple years of the same value, additional rows are returned for the subsequent years. I only want one row per change in the value.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources