Counting occurence of consecutive non-zero records

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)

Counting occurence of consecutive non-zero records

Author

Topic

mikip
Starting Member

4 Posts

Posted - 2009-07-31 : 10:43:53

Hi,

I am trying to achieve the following in SQL Server 2000 given this data.


YEAR	MONTH	ACCOUNT	STMNT	ARREARS
2008    3       701046  1       0.00
2008	4	701046	2	0.00
2008	5	701046	3	0.00
2008	6	701046	4	0.00
2008	7	701046	5	18.00
2008	8	701046	6	1.00

For each Account, I want to count backwards from the latest/Max Stmnt or Year/Month and count the number of consecutive occurences where the column arrears is > 0? In the example above,it would be 2 as both Stmnt 6 and 5 have values in consecutive months. As soon as there is a break in Arrears, this should not be included in the Count. Here is another example,


YEAR	MONTH	ACCOUNT	STMNT	ARREARS
2008    3       701046  1       0.00
2008	4	701046	2	0.00
2008	5	701046	3	0.00
2008	6	701046	4	10.00
2008	7	701046	5	0.00
2008	8	701046	6	18.00

this would yield a count of 1 as after Stmnt 6, there is a zero amount.

Any help would be greatly appreciated

Mike

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-31 : 15:03:21

This would be easier in SQL2005/8 but this will work


Create Table t1 (year int, month int, account int, stmt int, arrears smallmoney);
go

insert t1
SELECT 2008, 3, 701046, 1, 0.00 union all
SELECT 2008, 4, 701046, 2, 0.00 union all
SELECT 2008, 5, 701046, 3, 0.00 union all
SELECT 2008, 6, 701046, 4, 0.00 union all
SELECT 2008, 7, 701046, 5, 18.00 union all
SELECT 2008, 8, 701046, 6, 1.00
GO

-- Find Consecutive Month in arrears.
-- Assumes that if not in arrears the most recent stmt then count = 0

Declare @account int
Declare @maxYear int
Declare @maxMonth int

SET @account = 701046
-- find the most recent statement cycle
SELECT	@maxYear = MAX(year) FROM t1
SELECT @maxMonth = MAX(month) FROM t1 WHERE year = @maxYear

-- count consec months
SELECT	CASE WHEN (SELECT arrears from t1 where year = @maxYear and month = @maxMonth and account = @account) > 0
	THEN consec + 1
	ELSE consec
END as consecutiveMonths
FROM	(
	SELECT	count(*) consec
	FROM	t1 a
	LEFT JOIN	t1 b
	On	a.stmt = b.stmt + 1
	And	a.account = b.account
	WHERE	a.arrears > 0
	And	b.arrears > 0
	And	a.account = @account
) x

mikip
Starting Member

4 Posts

Posted - 2009-07-31 : 16:43:38

Hi Russell,

Thanks so much for this...How would i change the query if I have multiple accounts in this table, not just one..Sorry, i should have illutrated this in the data!

Thanks again for your help

Mike

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-31 : 17:18:05

I haven't tested this in all scenarios, but this should return all past due accounts.


Create Table t1 (year int, month int, account int, stmt int, arrears smallmoney);
go

insert t1
SELECT 2008, 3, 701046, 1, 0.00 union all
SELECT 2008, 4, 701046, 2, 0.00 union all
SELECT 2008, 5, 701046, 3, 0.00 union all
SELECT 2008, 6, 701046, 4, 0.00 union all
SELECT 2008, 7, 701046, 5, 18.00 union all
SELECT 2008, 8, 701046, 6, 1.00 union all
SELECT 2008, 3, 701047, 1, 0.00 union all
SELECT 2008, 4, 701047, 2, 0.00 union all
SELECT 2008, 5, 701047, 3, 0.00 union all
SELECT 2008, 6, 701047, 4, 0.00 union all
SELECT 2008, 7, 701047, 5, 18.00 union all
SELECT 2008, 8, 701047, 6, 0.00 union all
SELECT 2008, 3, 701048, 1, 0.00 union all
SELECT 2008, 4, 701048, 2, 0.00 union all
SELECT 2008, 5, 701048, 3, 0.00 union all
SELECT 2008, 6, 701048, 4, 1.00 union all
SELECT 2008, 7, 701048, 5, 18.00 union all
SELECT 2008, 8, 701048, 6, 1.00
GO

Declare @maxYear int
Declare @maxMonth int

SELECT	@maxYear = MAX(year) FROM t1
SELECT @maxMonth = MAX(month) FROM t1 WHERE year = @maxYear

SELECT	a.account, count(*) + 1 consec
FROM	t1 a
JOIN	t1 b
On	a.stmt = b.stmt + 1
And	a.account = b.account
JOIN	(
	SELECT	account
	FROM	t1
	WHERE	year = @MaxYear
	And	month = @maxMonth
	And	arrears > 0
) x
On	x.account = a.account
WHERE	a.arrears > 0
And	b.arrears > 0
group by
	a.account

mikip
Starting Member

4 Posts

Posted - 2009-07-31 : 17:46:06

Thanks again for the help Russell!

This seems almost there but I think it is returning the count of months in arrears even if there is a break in the consecutive month and it does not return when the latest month is in arrears and the previous is not i.e. In your test data for account 701047, if you make Month 8, 18 and Month 7, 0 then this should return this account with count of 1 but it's missing.

Many thanks in advance for all your help

Mike

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-31 : 20:57:48

yup, i didn't have time to test it, so I shouldn't have posted it. try this. i'm sure someone can come up with something more elegant, but I believe this returns the correct results:


Create Table #t (account int, consecMonths int);

Declare @maxYear int
Declare @maxMonth int

SELECT	@maxYear = MAX(year) FROM t1
SELECT @maxMonth = MAX(month) FROM t1 WHERE year = @maxYear

insert	#t
SELECT	a.account, count(*) + 1 consec
FROM	t1 a
JOIN	t1 b
On	a.stmt = b.stmt + 1
And	a.account = b.account
JOIN	(
	SELECT	account
	FROM	t1
	WHERE	year = @MaxYear
	And	month = @maxMonth
	And	arrears > 0
) x
On	x.account = a.account
WHERE	a.arrears > 0
And	b.arrears > 0
group by
	a.account

INSERT	#t
SELECT	a.account, 1
FROM	t1 a
JOIN	t1 b
On	a.stmt = b.stmt + 1
And	a.account = b.account
JOIN	(
	SELECT	account
	FROM	t1
	WHERE	year = @MaxYear
	And	month = @maxMonth
	And	arrears > 0
) x
On	x.account = a.account
LEFT JOIN
	#t t
On	t.account = a.account
WHERE	a.arrears > 0
And	b.arrears = 0
And	t.account is null
group by
	a.account

select * from #t
drop table #t

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-01 : 00:55:17

this is better and produces the correct results:


Declare @maxYear int
Declare @maxMonth int

SELECT	@maxYear = MAX(year) FROM t1
SELECT @maxMonth = MAX(month) FROM t1 WHERE year = @maxYear


SELECT	a.account, a.stmt - b.stmt as consecutiveMonths
FROM	(
	SELECT	account, stmt
	FROM	t1
	WHERE	arrears > 0
	And	month = @maxMonth
	And	year = @maxYear
) a
JOIN (
	SELECT	account, Max(stmt) stmt
	FROM	t1
	WHERE	arrears = 0
	GROUP BY
		account
) b
On	a.account = b.account

mikip
Starting Member

4 Posts

Posted - 2009-08-01 : 04:48:35

Spot on Russell!

Thanks for all the help...seems easy once you see the answer!

Cheers

Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources