Stack Overflow Asked on December 9, 2020
I am using SQL Server 2012 and want to get total of login unsuccessful attempts. My table has
| Login_Name | Is_Success | Login_Date |
My query is
select login_name,
sum(case when is_success = 1 THEN 0 ELSE 1 END) over (partition by login_name, issuccess order by login_name, login_date, is_success)
from login_table
The query gives me results but I want the count to be reset when is_success becomes 1
| Login_Name | Is_Success | Unsuccessful Attempts |
| admin | 1 | 0 |
| admin | 0 | 1 |
| admin | 0 | 2 |
| admin | 0 | 3 |
| admin | 0 | 4 |
| admin | 0 | 5 |
| admin | 1 | 0 |
| admin | 0 | 1 |
Note: I am using SQL Server 2012
I assume you're looking for a summary, not a log of specifics? I would approach that like so:
SELECT login_name,
( SELECT count(*) FROM login_table
WHERE login_name = lt.login_name
AND login_date > ( SELECT MAX(login_date) FROM login_table WHERE login_name = lt.login_name AND is_success = 1 )
) AS failures
FROM login_table AS lt
ORDER BY 1
How it works:
The inner correlated subquery gets the last successful login for a user:
SELECT MAX(login_date) FROM login_table WHERE login_name = lt.login_name AND is_success = 1
The outer correlated query:
( SELECT count(*) FROM login_table WHERE login_name = lt.login_name AND login_date > --last successful login-- ) AS failures
counts how many failures occurred after the last success. I have not tested this. Good luck with your project!
Answered by Jay on December 9, 2020
This is a classic gaps and island problem. You can use a windowed COUNT
to put the data into groups, and then use ROW_NUMBER
for those groups:
WITH YourTable AS(
SELECT *
FROM (VALUES('admin',1,CONVERT(datetime2(0),'2020-10-30T20:00:00')),
('admin',0,CONVERT(datetime2(0),'2020-10-30T20:01:00')),
('admin',0,CONVERT(datetime2(0),'2020-10-30T20:02:00')),
('admin',0,CONVERT(datetime2(0),'2020-10-30T20:03:00')),
('admin',0,CONVERT(datetime2(0),'2020-10-30T20:04:00')),
('admin',0,CONVERT(datetime2(0),'2020-10-30T20:05:00')),
('admin',1,CONVERT(datetime2(0),'2020-10-30T20:06:00')),
('admin',0,CONVERT(datetime2(0),'2020-10-30T20:07:00')))V(Login_Name,Is_Success,Login_Date)),
Grps AS(
SELECT Login_Name,
Is_Success,
Login_Date,
COUNT(CASE Is_Success WHEN 1 THEN 1 END) OVER (PARTITION BY Login_Name ORDER BY Login_Date) AS Grp
FROM YourTable)
SELECT Login_NAme,
Is_Success,
ROW_NUMBER() OVER (PARTITION BY Login_NAme, Grp ORDER BY Login_Date) -1 AS UnSuccessfulAttempts
FROM Grps
ORDER BY Login_Name,
Grp,
Login_Date;
Answered by Larnu on December 9, 2020
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP