Count the consecutive number of minutes
-
Hello everyone.
I am trying to do a query and I can't get at the end of it.
I have a table Statisticss with the folowing columns: Ip (varchar), dateT (date), Error (1 or 0)
I am trying to count for each ip the number of consecutive minutes that ip stayed in error.
Eg: Ip Datet Error
17.1.30.16 2005-07-19 12:08:00 0
17.1.30.16 2005-07-19 13:11:00 1
17.1.30.16 2005-07-19 13:12:00 1
10.80.20.10 2005-07-20 03:23:00 1
10.80.20.10 2005-07-21 03:24:00 1
10.80.20.10 2005-07-21 03:25:00 1
10.80.20.10 2005-07-21 03:26:00 0
10.80.20.10 2005-07-20 03:33:00 1
10.80.20.10 2005-07-21 03:34:00 1
This should return
17.1.30.16 2005-07-19 13:11:00 2005-07-19 13:12:00 1 min
10.80.20.10 2005-07-20 03:23:00 2005-07-20 03:26:00 3 min
10.80.20.10 2005-07-20 03:33:00 2005-07-20 03:34:00 3 min
I'd appreciate any help... Thank you in advance
-
I think you have some errors in your data - '2005-07-21' should probably be '2005-07-20'. Anyway, I have put together a query that might give you what you want. It returns '03:25' instead of '03:26', but I hope that's OK
Otherwise, please specify what the end date should be.create table Statisticss (Ip varchar(20), Datet datetime, Error bit)
go
insert into Statisticss select
'17.1.30.16', '2005-07-19 12:08:00', 0
union all select
'17.1.30.16', '2005-07-19 13:11:00', 1
union all select
'17.1.30.16', '2005-07-19 13:12:00', 1
union all select
'10.80.20.10', '2005-07-20 03:23:00', 1
union all select
'10.80.20.10', '2005-07-20 03:24:00', 1
union all select
'10.80.20.10', '2005-07-20 03:25:00', 1
union all select
'10.80.20.10', '2005-07-20 03:26:00', 0
union all select
'10.80.20.10', '2005-07-20 03:33:00', 1
union all select
'10.80.20.10', '2005-07-20 03:34:00', 1
select s1.ip, s1.datet, min(s2.datet) from statisticss s1 inner join statisticss s2
on s1.ip = s2.ip
where
s1.error = 1 and s2.error = 1 and s1.datet < s2.datet
and not exists(
select * from statisticss s3
where s3.ip = s1.ip and
s3.datet = dateadd(mi, -1, s1.datet) and
s3.error = 1)
and not exists(
select * from statisticss s4
where s4.ip = s2.ip and
s4.datet = dateadd(mi, 1, s2.datet) and
s4.error = 1)
group by s1.ip, s1.datet
-
I have to display the number of minutes between dates also. The end date is ok. Could you tell me how could I add that to my select? Thanks for the help
-
select s1.ip, s1.datet, min(s2.datet), cast(datediff(mi, s1.datet, min(s2.datet)) as varchar) + ' mins'
from statisticss s1 inner join statisticss s2
on s1.ip = s2.ip
where
s1.error = 1 and s2.error = 1 and s1.datet < s2.datet
and not exists(
select * from statisticss s3
where s3.ip = s1.ip and
s3.datet = dateadd(mi, -1, s1.datet) and
s3.error = 1)
and not exists(
select * from statisticss s4
where s4.ip = s2.ip and
s4.datet = dateadd(mi, 1, s2.datet) and
s4.error = 1)
group by s1.ip, s1.datet
-
I just solved the problem also... Thank you very much Jesper Myqind
You were a great help
-
No problem, I enjoyed solving it...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply