Comparing value to prior value

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

SQL Server Development (2000)

Comparing value to prior value

Author

Topic

Bunks
Starting Member

10 Posts

Posted - 2010-05-17 : 00:41:11

Hi all

Quick question - I hope this is pretty simple.

I have a database set up with employee id, effective date and a salary amount per fortnight (eg. below):

ID Eff Date Salary
12 01/04/2009 2,000
12 15/04/2009 2,200
12 29/04/2009 2,200
13 01/04/2009 1,500
13 15/04/2009 1,400
13 29/04/2009 1,700

I want to be able to compare one salary to the previous salary (per ID no.) to see if the variance is greater than 10%.

Any help would be greatly appreciated.

Thanks - Luke

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-17 : 12:30:53

[code]
SELECT ID,EffDate,
Salary,
(Salary - COALESCE(PrevSalary,0))*100.0/NULLIF(PrevSalary,0) AS [Variance%]
FROM
(
SELECT t.ID,
t.EffDate,
t.Salary,
(SELECT TOP 1 Salary FROM Table WHERE ID = t.ID AND EffDate < t.effDate ORDER BY EffDate DESC) AS PrevSalary
FROM Table t
)m
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bunks
Starting Member

10 Posts

Posted - 2010-05-18 : 00:09:43

Thanks Visakh but I still can't seem to get there.

Below is my actual script:

SELECT
q1.id,
s.SalaryEffectiveDate,
s.AnnualSalary,
(s.AnnualSalary - COALESCE(PrevSalary,0))*100.0/NULLIF(PrevSalary,0) as [Variance%]

from Query001Suba q1
join (select id, SalaryEffectiveDate, AnnualSalary, (select top 1 s.AnnualSalary
from QuerySubSalary
where q1.id = s.id and s.SalaryEffectiveDate < SalaryEffectiveDate
order by s.SalaryEffectiveDate desc) as PrevSalary from QuerySubSalary s) m

I get a "incorrect syntax near 'm'" error?

Luke

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-18 : 01:12:10

where's on condition for join?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources