challenge - alternative way of doing SQL 1

[fredericofonseca]

Hi all,

been ask to do a small audit table of logins, and respective reports, and I have come out with some SQL which does do what I need. But can't help and wonder if there would be another way of doing it without using cursors or temporary tables.

sample schema, data and code below.

requirement is to report any user that on a given day has had at least 3 consecutive fails.
Code below gives the expected output

USERID	AUDIT_DATE
USER3	2011/10/19
USER2	2011/10/19

and if change the outer select to include all fields without distinct

USERID	AUDIT_DATE	ID	FLAG	T1	T2
USER2	2011/10/19	6	fail	2	2  <- correct - 2 previous consecutive fails and current record is fail also
USER2	2011/10/19	11	fail	3	2  <- correct - 3 previous consecutive fails (current record fail just reinforces selection
USER2	2011/10/19	12	ok	3	2  <- correct - 3 previous consecutive fails
USER2	2011/10/19	25	fail	2	2  <- correct - 2 previous consecutive fails and current record is fail also
USER3	2011/10/19	20	fail	2	2  <- correct - 2 previous consecutive fails and current record is fail also
USER3	2011/10/19	22	fail	3	2  <- correct - 2 previous consecutive fails (current record fail just reinforces selection
USER3	2011/10/19	24	ok	3	2  <- correct - 3 previous consecutive fails
USER3	2011/10/19	33	fail	2	2  <- correct - 2 previous consecutive fails (current record fail just reinforces selection

Would be interested in seeing the alternatives you lot come back with

drop table  test1 purge;
create table test1
(id number(9)
,userid varchar2(10)
,audit_stamp date
,flag varchar2(10)
);
insert into test1 values(1,'USER1',sysdate,'ok');
insert into test1 values(2,'USER1',sysdate,'ok');
insert into test1 values(3,'USER2',sysdate,'fail');
insert into test1 values(4,'USER1',sysdate,'fail');
insert into test1 values(5,'USER1',sysdate,'ok');
insert into test1 values(6,'USER2',sysdate,'fail');
insert into test1 values(7,'USER1',sysdate,'fail');
insert into test1 values(8,'USER1',sysdate,'ok');
insert into test1 values(9,'USER1',sysdate,'ok');
insert into test1 values(0,'USER2',sysdate,'fail');
insert into test1 values(11,'USER2',sysdate,'fail');
insert into test1 values(12,'USER2',sysdate,'ok');
insert into test1 values(13,'USER2',sysdate,'ok');
insert into test1 values(14,'USER3',sysdate,'fail');
insert into test1 values(15,'USER3',sysdate,'fail');
insert into test1 values(16,'USER2',sysdate,'ok');
insert into test1 values(17,'USER2',sysdate,'ok');
insert into test1 values(18,'USER2',sysdate,'ok');
insert into test1 values(19,'USER2',sysdate,'ok');
insert into test1 values(20,'USER3',sysdate,'fail');
insert into test1 values(21,'USER2',sysdate,'fail');
insert into test1 values(22,'USER3',sysdate,'fail');
insert into test1 values(23,'USER2',sysdate,'fail');
insert into test1 values(24,'USER3',sysdate,'ok');
insert into test1 values(25,'USER2',sysdate,'fail');
insert into test1 values(26,'USER3',sysdate,'ok');
insert into test1 values(27,'USER3',sysdate,'ok');
insert into test1 values(28,'USER3',sysdate,'ok');
insert into test1 values(29,'USER3',sysdate,'ok');
insert into test1 values(30,'USER3',sysdate,'ok');
insert into test1 values(31,'USER3',sysdate,'fail');
insert into test1 values(32,'USER3',sysdate,'fail');
insert into test1 values(33,'USER3',sysdate,'fail');

commit;


select distinct userid, audit_date
from (
select 
 userid
 ,to_char(audit_stamp,'YYYY/MM/DD') audit_date
 ,id
 ,flag
 , case when lag(flag,1,0) over(partition by to_char(audit_stamp,'YYYY/MM/DD'),userid order by  id ) = 'fail' then 1 else 0 end
 +  case when lag(flag,2,0) over(partition by to_char(audit_stamp,'YYYY/MM/DD'),userid order by  id ) = 'fail' then 1 else 0 end
 +  case when lag(flag,3,0) over(partition by to_char(audit_stamp,'YYYY/MM/DD'),userid order by  id ) = 'fail' then 1 else 0 end as t1
 , case when lag(flag,1,0) over(partition by to_char(audit_stamp,'YYYY/MM/DD'),userid order by  id ) = 'fail' then 1 else 0 end
 +  case when lag(flag,2,0) over(partition by to_char(audit_stamp,'YYYY/MM/DD'),userid order by  id ) = 'fail' then 1 else 0 end as t2
 
from test1
--where audit_stamp >= to_date('19/10/2011 21:25:29', 'DD/MM/YYYY HH24:MI:SS')
) t
where t1 > 2
or flag = 'fail' and t2 = 2

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Dagon]

I think something like this would be more concise and accurate:

select distinct userid,to_char(audit_stamp,'YYYY/MM/DD') audit_date
from
(select id, userid, audit_stamp, flag,
count(case flag when 'fail' then flag else null end) over (partition by userid order by id rows between 2 preceding and current row) logfails
from test1)
where logfails > 2

It produces basically the same results as yours but it's slightly better in that it doesn't return rows like id=12, which is a success but happens to have three failures behind it.

 

[fredericofonseca]

Thanks Dragon,


Sorry for late reply but was just waiting to see if any other solution would come out.

You solution, with the addition of the day validation to the partition clause, is a lot clearer.
I had been looking around similar but failed somehow on the windowing clause



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886