SQL - add a flag based on previous rows

Question

Hi hopefully someone can help with this. I have a table that looks like the following.

 Case_ID        Task_ID        Created_Date       Created_By
  4567           5654          01-Jun-2018         John.Doe
  4567           2324          02-Jun-2018         John.Doe
  4567           8634          05-Jun-2018         Jane.Doe
  4567           2444          20-Jun-2018         Sharon.Test
  4567           8953          25-Jun-2018         Max.Test
  4567           2444          27-Jun-2018         John.Doe
  4567           8953          30-Jun-2018         Pamela.Test
  4567           6546          01-Aug-2018         John.Doe
  4567           7566          02-Aug-2018         Steven.Ely
  4567           7666          07-Aug-2018         Jane.Doe
  4567           9087          07-Aug-2018         Sharon.Test
  4567           5433          13-Aug-2018         Max.Test
  4567           4321          13-Aug-2018         John.Doe
  4567           1242          30-Aug-2018         Pamela.Test

Each Case_ID is a single case. Whenever a user works on this case a new Task_ID gets added along with the Created_Date of this Task_ID and the username of the person who created it. I need an additional column, a flag which shows if a case has been worked on by 4 or more distinct consultants within a 14 day period. The output I would need from the above would look like the below

Case_ID        Task_ID        Created_Date       Created_By        Flag
  4567           5654          01-Jun-2018         John.Doe         0
  4567           2324          02-Jun-2018         John.Doe         0
  4567           8634          05-Jun-2018         Jane.Doe         0
  4567           2444          20-Jun-2018         Sharon.Test      0
  4567           8953          25-Jun-2018         Max.Test         0
  4567           2444          27-Jun-2018         John.Doe         0
  4567           8953          30-Jun-2018         Pamela.Test      1
  4567           6546          01-Aug-2018         John.Doe         0
  4567           7566          02-Aug-2018         Steven.Ely       0
  4567           7666          07-Aug-2018         Jane.Doe         0
  4567           9087          07-Aug-2018         Sharon.Test      1
  4567           5433          13-Aug-2018         Max.Test         1
  4567           4321          13-Aug-2018         John.Doe         1
  4567           1242          30-Aug-2018         Pamela.Test      0

So to explain the above, the reason that Pamela.Test was the first person to get the flag was because she was the 4th distinct person to work on Case_ID 4567 within a 14 day period, the first being Sharon.Test on 20-Jun-2018. This flag resets back to 0 on the 1st of August for John.Doe as it has been more than 14 days since Pamela.Test worked on it. Also, the Created_Date column is datetime, so some days can have more than 1 Task_ID associated to them

Does this make sense? I don't really know where to start with this and would appreciate any help as I will be working on projects like this going forward so it would be helpful :) Thanks, Jess.

EDIT -

Example output of query giving wrong output from below solution. Not sure why Gareth.Opal has the flag when he was the first and only person to add a Task_ID to 1002982

Answer 1

I would use a correlated subquery:

select t.*,
       (case when (select count(distinct t2.created_by)
                   from t t2
                   where t2.case_id = t.case_id and
                         t2.created_date <= t.created_date and
                         t2.created_date > dateadd(day, -14, t.created_date)
                  ) >= 4
             then 1 else 0
         end) as four_consultant_flag
from t;

Answer 2

Something like this:-

CREATE TABLE #caseTable (
    Case_ID VARCHAR(10)
    ,Task_ID VARCHAR(10)
    ,Created_Date DATETIME
    ,Created_By VARCHAR(50)
    )

INSERT INTO #caseTable
VALUES (
    4567
    ,5654
    ,'01-Jun-2018'
    ,'John.Doe'
    )
    ,(
    4567
    ,2324
    ,'02-Jun-2018'
    ,'John.Doe'
    )
    ,(
    4567
    ,8634
    ,'05-Jun-2018'
    ,'Jane.Doe'
    )
    ,(
    4567
    ,2444
    ,'20-Jun-2018'
    ,'Sharon.Test'
    )
    ,(
    4567
    ,8953
    ,'25-Jun-2018'
    ,'Max.Test'
    )
    ,(
    4567
    ,2444
    ,'27-Jun-2018'
    ,'John.Doe'
    )
    ,(
    4567
    ,8953
    ,'30-Jun-2018'
    ,'Pamela.Test'
    )
    ,(
    4567
    ,6546
    ,'01-Aug-2018'
    ,'John.Doe'
    )
    ,(
    4567
    ,7566
    ,'02-Aug-2018'
    ,'Steven.Ely'
    )
    ,(
    4567
    ,7666
    ,'07-Aug-2018'
    ,'Jane.Doe'
    )
    ,(
    4567
    ,9087
    ,'07-Aug-2018'
    ,'Sharon.Test'
    )
    ,(
    4567
    ,5433
    ,'13-Aug-2018'
    ,'Max.Test'
    )
    ,(
    4567
    ,4321
    ,'13-Aug-2018'
    ,'John.Doe'
    )
    ,(
    4567
    ,1242
    ,'30-Aug-2018'
    ,'Pamela.Test'
    )
    ,(
    4568
    ,1005
    ,'30-Aug-2018'
    ,'D.Test'
    )
    ,(
    4568
    ,1002
    ,'30-Aug-2018'
    ,'C.Test'
    )
    ,(
    4568
    ,1000
    ,'30-Aug-2018'
    ,'B.Test'
    )
    ,(
    4568
    ,1001
    ,'30-Aug-2018'
    ,'A.Test'
    )

SELECT m.case_id
    ,m.task_id
    ,m.created_date
    ,m.created_by
    ,
    (select count(distinct s.Created_By) from 
    (select *,ROW_NUMBER() over( order by case_id, created_date) as rowNum from #caseTable)
    s
    where s.Created_Date between dateadd(D,-14,m.Created_Date) and m.Created_Date and s.rowNum<=m.rowNum
     and s.Case_ID=m.Case_ID) as uniqueCount,
    IIF((
            SELECT count(DISTINCT s.Created_By)
            FROM (
                SELECT *
                    ,ROW_NUMBER() OVER (
                        ORDER BY case_id
                            ,created_date
                        ) AS rowNum
                FROM #caseTable
                ) s
            WHERE s.Created_Date BETWEEN dateadd(D, - 14, m.Created_Date)
                    AND m.Created_Date
                AND s.rowNum <= m.rowNum  and s.Case_ID=m.Case_ID
            ) >= 4, 1, 0) AS flag
FROM (
    SELECT *
        ,ROW_NUMBER() OVER (
            ORDER BY case_id
                ,created_date
            ) AS rowNum
    FROM #caseTable
    ) m

DROP TABLE #caseTable