I have a table that looks like below. Every time a person speaks up, there will be a new row containing the date, the person who spoke up and a comment ID which is unique to every comment. The table has 3 years of data.

What I would like to do is to find a solution in SQL that elegantly picks up persons who spoke up more than 6 times within 2 days time frame in the past 1 year.

For instance, from the example table, what I would like to see are:

• A: 2021-10-20 & 2021-10-19, 6 times
• B: 2021-10-19 & 2021-10-18, 7 times

What's irrelevant are:

• B: 2021-10-20 & 2021-10-19, 3 times
• A: 2021-10-19 & 2021-10-18, 4 times

CodePudding user response：

Let's take a record, "2021-10-20 | A" and join with the same table to find other rows within the 2 days range. Aggregate them (Group By) and filter (Having)...

SELECT DISTINCT
    C1.[Date] AS FromDate,
    C1.[Date] 1 AS ToDate,
    C1.[Person],
    COUNT(1)
FROM Comments C1
JOIN Comments C2
      ON  C2.Person >= C1.Person
      and C2.[Date] >= C1.[Date]
      and C2.[Date] <= C1.[Date] 1
GROUP BY C1.[Date], C1.[Person]
HAVING COUNT(1) >= 6

      

CodePudding user response：

Take a look at this:

declare @MyTable as table (
    [Date] datetime,
    Person varchar(3),
    CommentID varchar(50)
)

INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('20/10/21', 'A', 'fjeiwarjea')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('20/10/21', 'B', 'ahfeawas')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('20/10/21', 'A', 'iewaokdow')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('20/10/21', 'A', 'oweidarek')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('20/10/21', 'A', 'sqoeidke')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('19/10/21', 'B', 'qejacjes')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('19/10/21', 'A', 'voewaiekd')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('19/10/21', 'B', 'saoweikladf')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('19/10/21', 'A', 'poewieakre')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('18/10/21', 'A', 'biewaldcwe')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('18/10/21', 'A', 'deaireal')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('18/10/21', 'B', 'zfdewoaierje')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('18/10/21', 'B', 'kfewajireuifd')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('18/10/21', 'B', 'mfeaiwruei')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('18/10/21', 'B', 'wrfeiarjeilwaf')
INSERT INTO @MyTable ([Date], Person, CommentID) VALUES ('18/10/21', 'B', 'yhfewaurhdfj');

with Comments (FromDate, ToDate, Person, Times)
as
(
    select t1.[Date] FromDate, DATEADD(d, -1, t1.[date]) ToDate, t1.Person, 
    (
        select COUNT(*) from @MyTable t2 where t2.Person = t1.Person and t2.[Date] between DATEADD(d, -1, t1.[date]) and t1.[date]
    ) Times
    from @MyTable t1
    where t1.[Date] > DATEADD(yy, -1, getdate()) -- just last year
    group by t1.[Date], DATEADD(d, -1, t1.[date]), t1.Person
)
select * from Comments where Times >= 6 

This will return this:

I think the code is self explained, but if you need some explanation I can update the answer.