CODEWITHSUNDEEP
sql-serversql-server-2008
Source Matters
Source Matters

Reputation: 1221

Determine longest "streak" in SQL Server?

I looked at some of the other "Streak" questions, but nothing was quite the same as my situation as theirs included consecutive ID's.

I have the data shown below and I am attempting to determine the longest "day" streak each user has had of "Yes" proficiency without receiving a "No". The days aren't necessarily consecutive. Each day is effectively a work "Shift", so I'd like to be able to say "User X has had 8 shifts without a 'No'." for each user.

ProficiencyUser ProficiencyDateAdded       ProficiencyMet
JDOE            2016-04-09 19:35:51.590    Yes
RTHOMPSON       2016-04-09 19:16:42.237    Yes
MMOUSE          2016-04-09 18:59:03.263    Yes
MMOUSE          2016-04-09 18:54:33.210    Yes
MMOUSE          2016-04-09 18:26:55.750    No
MMOUSE          2016-04-06 18:54:33.210    Yes
MMOUSE          2016-03-26 13:54:33.210    Yes
MMOUSE          2016-04-09 18:54:33.210    Yes

I am using SQL Server 2008 R2.

Upvotes: 1

Views: 318

Answers (2)

Sergei Zinovyev
Sergei Zinovyev

Reputation: 1286

This query returns the longest streak for each user:

;WITH
TestUser AS -- Convert 'Yes'/'No' to int
(
SELECT
    t.[ProficiencyUser]
    ,t.[ProficiencyDateAdded]
    ,ProficiencyMet = CASE WHEN t.[ProficiencyMet] = 'Yes' THEN 1 ELSE 0 END
FROM [Test] t
)
,ByDates AS -- Get Result per Day per User
(
SELECT
    u.[ProficiencyUser]
    ,ProficiencyDate = CAST(u.[ProficiencyDateAdded] AS date)
    ,ProficiencyMet = CASE WHEN COUNT(u.[ProficiencyMet]) = SUM(CAST(u.[ProficiencyMet] as int)) THEN 1 ELSE 0 END
FROM [TestUser] u
GROUP BY [u].[ProficiencyUser], CAST(u.[ProficiencyDateAdded] AS date)
)
,UserDayRank AS -- rank user days
(
SELECT
    Id = RANK() OVER(PARTITION BY [ProficiencyUser] ORDER BY [ProficiencyDate])
    ,[ProficiencyUser]
    ,[ProficiencyDate]
    ,[ProficiencyMet]
FROM [ByDates]
)
,UserSeq AS -- filter user days, and get islands and gaps per user
(
SELECT
    [Id]
    ,[ProficiencyUser]
FROM [UserDayRank]
WHERE [ProficiencyMet] = 1
)
,Islands AS -- number each user's island
(
SELECT
    Id
    ,GroupId = Id - ROW_NUMBER() OVER(PARTITION BY [ProficiencyUser] ORDER BY [Id])
    ,[ProficiencyUser]
FROM [UserSeq]
)
,IslandLen AS -- get length of user's islands
(
SELECT
    L = COUNT([GroupId])
    ,[ProficiencyUser]
FROM [Islands]
GROUP BY [ProficiencyUser], [GroupId]
)
-- finally get the longest user's island for each user
SELECT [ProficiencyUser], L = MAX(L) FROM [IslandLen]
GROUP BY [ProficiencyUser]
ORDER BY MAX(L) DESC, [ProficiencyUser]

Upvotes: 1

Source Matters
Source Matters

Reputation: 1221

In the modified requirements (see comments), here is what I came up that gives me the "streak" (number of days consecutively) from today's date to each user's last "No" entry:

SELECT l.*, DATEDIFF(DAY, l.ProficiencyDateAdded, GETDATE()) AS 'DaysAgo'
FROM
(SELECT ProficiencyUser, MAX(ProficiencyDateAdded) AS 'ProficiencyDateAdded'
FROM   ProficiencyTable
WHERE ProficiencyMet LIKE 'No'
GROUP BY ProficiencyUser) l
ORDER BY DaysAgo DESC

Upvotes: 0

Related Questions