Counting consecutive # of days a condition is true

Hello,
I have a table. Among the fields are ServerName, ProcessDate and ErrorNumber. What I'd like is a select query which will generate a new field which counts back the consecutive number of days that ErrorNumber \= 0. I'd like the record set to include all Se
rverNames for each ProcessDate arguement.
In other words, the field would show a 5 if the Error Number is 1 for 5 days in a row.
I've been pulling my hair out about this.
Thanks!
Stewart,
Could you post some DDL and sample data? Also, by 'consecutive', do you
mean actual days or only business days?
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:89FFEBA8-ED1B-4B6B-837D-5A5F3A7C866C@.microsoft.com...
> Hello,
> I have a table. Among the fields are ServerName, ProcessDate and
ErrorNumber. What I'd like is a select query which will generate a new field
which counts back the consecutive number of days that ErrorNumber \= 0. I'd
like the record set to include all ServerNames for each ProcessDate
arguement.
> In other words, the field would show a 5 if the Error Number is 1 for 5
days in a row.
> I've been pulling my hair out about this.
> Thanks!
|||Hope this works for you... It's not the most elegant code but if I
understand your problem it should return what you need:
create table #process(servername char(1), servicedate datetime, errornum
int)
GO
insert #process values ('A', '20040101', 1)
insert #process values ('A', '20040102', 1)
insert #process values ('A', '20040103', 0)
insert #process values ('A', '20040104', 0)
insert #process values ('A', '20040105', 1)
insert #process values ('B', '20040101', 0)
insert #process values ('B', '20040102', 1)
insert #process values ('B', '20040103', 1)
insert #process values ('B', '20040104', 0)
insert #process values ('B', '20040105', 0)
insert #process values ('B', '20040106', 1)
insert #process values ('B', '20040107', 1)
insert #process values ('B', '20040108', 1)
insert #process values ('B', '20040109', 1)
GO
select *, case errornum when 0 then 0 else
(select count(*)
from #process b
where b.servername=a.servername
and b.errornum <> 0
and b.servicedate <= a.servicedate
and CASE WHEN EXISTS
(SELECT * FROM #process d
where d.servicedate < a.servicedate
and d.servername=a.servername
and d.errornum=0) THEN
CASE WHEN b.servicedate >
(select max(servicedate)
from #process c
where c.servicedate < a.servicedate
and c.errornum=0
and c.servername=a.servername) THEN 1
else 0 end
else 1 end = 1) end
from #process a
GO
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:89FFEBA8-ED1B-4B6B-837D-5A5F3A7C866C@.microsoft.com...
> Hello,
> I have a table. Among the fields are ServerName, ProcessDate and
ErrorNumber. What I'd like is a select query which will generate a new field
which counts back the consecutive number of days that ErrorNumber \= 0. I'd
like the record set to include all ServerNames for each ProcessDate
arguement.
> In other words, the field would show a 5 if the Error Number is 1 for 5
days in a row.
> I've been pulling my hair out about this.
> Thanks!
|||Wow Adam! Thanks a tonne for this. In lookin at this, I understand the jist of what its doin. But I'm no database dynamo, so I'm gonna have to work with this for a bit to see what it'll actually return.
Thanks again!
|||Here's a slightly simplified version of the query:
select *, case errornum when 0 then 0 else
(select count(*)
from #process b
where b.servername=a.servername
and b.errornum <> 0
and b.servicedate <= a.servicedate
and b.servicedate >
coalesce((select max(servicedate)
from #process c
where c.servicedate < a.servicedate
and c.errornum=0
and c.servername=a.servername),
b.servicedate - 1)
) end
from #process a
GO
"Stewart" <anonymous@.discussions.microsoft.com> wrote in message
news:2BC7AFB0-03E9-413B-8733-FABA0170CFFD@.microsoft.com...
> Wow Adam! Thanks a tonne for this. In lookin at this, I understand the
jist of what its doin. But I'm no database dynamo, so I'm gonna have to work
with this for a bit to see what it'll actually return.
> Thanks again!
|||Thats exactly it! I gotta be able to pass a parameter for each servicedate, but I'll be able to figure that out.
Thanks - I appreciate it!