fetch consecutive records on a specific criteria

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)

fetch consecutive records on a specific criteria

Author

Topic

MH
Starting Member

8 Posts

Posted - 2014-07-10 : 01:27:58

Hello,

I have a transaction table. It has records like this:

ID Response Amount Channel DateTime
1 Approved 100 POS 2014-07-10 00:00:00
2 Declined 200 POS 2014-07-10 01:00:00
3 Declined 300 POS 2014-07-10 01:10:00
4 Approved 400 POS 2014-07-10 01:12:00
5 Declined 500 POS 2014-07-10 01:30:00

I get the declined transactions in last ten minutes by:

select * from transaction
where Response='Declined'
and DATEDIFF(Minute,DateTime ,GetDate()) < 10

I want to get the consecutive declined transactions in last 10 minutes.
How can I get them.Please guide.

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-10 : 10:41:15

quote:
Originally posted by MH

Hello,

I have a transaction table. It has records like this:

ID Response Amount Channel DateTime
1 Approved 100 POS 2014-07-10 00:00:00
2 Declined 200 POS 2014-07-10 01:00:00
3 Declined 300 POS 2014-07-10 01:10:00
4 Approved 400 POS 2014-07-10 01:12:00
5 Declined 500 POS 2014-07-10 01:30:00

I get the declined transactions in last ten minutes by:

select * from transaction
where Response='Declined'
and DATEDIFF(Minute,DateTime ,GetDate()) < 10

I want to get the consecutive declined transactions in last 10 minutes.
How can I get them.Please guide.

Thanks

There are probably more elegant and brilliant ways to do this, but here is something quick (and not necessarily dirty)

;WITH cte1 AS
(
	SELECT *, 
	ROW_NUMBER() OVER (ORDER BY id) - ROW_NUMBER() OVER (PARTITION BY response ORDER BY id) AS GN
	FROM YourTable
), cte2 as ( SELECT *, COUNT(*) OVER (PARTITION BY GN, response) N FROM cte1 WHERE response = 'Declined')
SELECT * FROM cte2 WHERE N > 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources