Find out 2 consecutive even numbers in a column

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)

Find out 2 consecutive even numbers in a column

Author

Topic

SharadD
Starting Member

4 Posts

Posted - 2008-09-18 : 03:42:54

Hi,

I want to find out 2 consecutive even numbers in a column.

Eg,

If my table contains following data,

Name Age
Prakash 27
Vikas 24
Aakash 23
Vinod 28
Pramod 32
Shinod 25

Then I should get the 2 consecutive Even numbered ages as 28 and 32
but not 24 and 28 because 23 is an odd number in between them.

Please help me to get this query run perfectly.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-18 : 03:44:21

home work ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 03:48:18

can we see what you tried till now?

SharadD
Starting Member

4 Posts

Posted - 2008-09-18 : 03:52:38

quote:
Originally posted by visakh16

can we see what you tried till now?

I have used cursors for the same and achieved my goal. But I want some optimized way to do that because I am using it in a stored procedure which is greater than 10000 lines.

What I did is this:

Go row-by-row through the records, If an even number found then increment the flag by 1 and if an odd number is found then set the flag to 0. When flag = 2 then you got 2 consucutive numbers.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-18 : 04:20:26

What is the ordering of the records ? by Name or Age ?

KH
[spoiler]Time is always against us[/spoiler]

SharadD
Starting Member

4 Posts

Posted - 2008-09-18 : 05:35:23

quote:
Originally posted by khtan

What is the ordering of the records ? by Name or Age ?

KH
[spoiler]Time is always against us[/spoiler]

Ordering is done by Name

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-18 : 09:44:33

[code]DECLARE @sample TABLE
(
Name varchar(10),
Age int
)
INSERT INTO @sample
SELECT 'Prakash', 27 UNION ALL
SELECT 'Vikas', 24 UNION ALL
SELECT 'Aakash', 23 UNION ALL
SELECT 'Vinod', 28 UNION ALL
SELECT 'Pramod', 32 UNION ALL
SELECT 'Shinod', 25

SELECT *
FROM @sample
ORDER BY Name

/*
Name Age
---------- -----------
Aakash 23
Prakash 27
Pramod 32
Shinod 25
Vikas 24
Vinod 28

(6 row(s) affected)
*/[/code]

As can seen from the result ORDER BY Name, the 2 consecutive even numbered ages should be Vikas with 24 and Vinod with 28 which is different from what you said in your 1st post.

Care to explain ?

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-18 : 10:12:36

If there is no order,

DECLARE @sample TABLE
(
id int identity(1,1),
Name varchar(10),
Age int
)
INSERT INTO @sample(name,age)
SELECT 'Prakash', 27 UNION ALL
SELECT 'Vikas', 24 UNION ALL
SELECT 'Aakash', 23 UNION ALL
SELECT 'Vinod', 28 UNION ALL
SELECT 'Pramod', 32 UNION ALL
SELECT 'Shinod', 25

SELECT t1.age as age1,t2.age as age2
FROM @sample as t1 inner join @sample as t2
on t1.id+1=t2.id where (t1.age-t2.age)%2=0

Madhivanan

Failing to plan is Planning to fail

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-09-18 : 11:04:35

You might want to test your solution on some data before posting it, madhivanan.
(33-31)%2=0

Boycotted Beijing Olympics 2008

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-18 : 11:36:45

quote:
Originally posted by blindman

You might want to test your solution on some data before posting it, madhivanan.
(33-31)%2=0

Boycotted Beijing Olympics 2008

Thanks Blindman. That should be where t1.age%2=0 and t2.age%2=0

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources