compare values on diffferent rows

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)

compare values on diffferent rows

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-12-10 : 06:10:30

hi guys,

Is if possible to write a query that compares values on different rows ?

ie.
Month number of customers % increase
1 45
2 67
3 100

eg. for the % increase I need to do 67 / 45 for month 2.

thank you for any help.
Jamie

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-10 : 06:27:21

[code]-- prepare sample data
declare @t table
(
month int,
cust numeric(25,6)
)

insert into @t
select 1, 45 union all
select 2, 67 union all
select 3, 100

-- Final query
Select *, ([current]-old)/old * 100.00 as Percent_Increase
from
(
select t1.month, t1.cust as [Current], isnull((select top 1 cust from @t t2 where t2.month < t1.month order by t2.month desc), t1.cust) as Old
from @t t1
) Temp[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 06:28:47

A 45-to-67 step is 48.89% increase, with (NewValue - OldValue) / OldValue.

There are pleny of examples from last two weeks how to do calculations involving previous records.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 06:46:20

Another way to solve this

SELECT		q.CurrentMonth,
		q.CurrentValue,
		100.0 * (q.CurrentValue - t.Cust) / t.Cust AS Increase
FROM		(
			SELECT	Mnth AS CurrentMonth,
				Mnth - 1 AS PreviousMonth,
				Cust AS CurrentValue
			FROM	@t
		) AS q
LEFT JOIN	@t AS t ON t.Mnth = q.PreviousMonth

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-10 : 06:55:45

But what if months are not consecutive?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 06:59:04

OP didn't say. But he did explain he wanted last month's value compared to current month's value.
So, if there is no previous month, the value can't be calculated and (in my opinion) should return NULL.

E 12°55'05.25"
N 56°04'39.16"

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-12-10 : 07:07:04

thank you, this is great.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-12-10 : 07:35:44

Another way

select t2.month,t2.cust,(select (t1.cust/t2.cust * 100)-100 from @t
t2 where t1.month=t2.month+1)as percentdiff
from @t t2 inner join @t t1 on t1.month=t2.month

Regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-10 : 08:10:21


Select *, ([current]-old)/old * 100.00 as Percent_Increase
from 
	(
		select t1.month,t1.cust as [current],coalesce(t2.cust,t1.cust) as old 
		from @t t1 left outer join @t t2 on t1.month=t2.month+1
) as t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources