Count based on data in row above

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

Transact-SQL (2008)

Count based on data in row above

Author

Topic

mugen2005
Starting Member

16 Posts

Posted - 2011-12-08 : 08:00:27

Checking row above for a count, is this possible? I want to count the X’s under activity ONLY if the row above is a B. Looking at the sample data there are 3 X’s, however with the query I would like to return only the 2 X’s which have ‘B’ about it.

Any help would be appreciated,

Thank you.

ID activity
1 A
2 B
3 X
4 A
5 A
6 B
7 A
8 X
9 B
10 X
11 A
12 A

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-08 : 08:06:12

[code]
select *
from table t1
inner join table t2 on t1.ID = t2.ID - 1
where t1.activity = 'B'
and t2.activity = 'X'
[/code]

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

mugen2005
Starting Member

16 Posts

Posted - 2011-12-08 : 09:01:16

worked perfect, thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources