SQL Server 2012 - Rolling counter with reset

Question

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

Jay · Answer

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!

Larnu · Answer

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;

SQL Server 2012 - Rolling counter with reset

2 Answers

Add your own answers!

Ask a Question