Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Help in finding events in two-week period RE: Help in finding events in two-week period

  • October 17, 2017 at 11:28 am

    #1964076

    dhanekulakalyan - Tuesday, October 17, 2017 10:04 AM

    Hello everyone,

    I have an issue in pulling out members having three or more events in a two-week period.

    for example in the below table, id 111 is having 3 events in two week period. i.e; ranks 2,3,4 event dates are in 2 week period. so we need to pull this member.

    Similarly for id 222 is having 5 events in 2 week period i.e;ranks with 1,2,3,4,5 , so we need to pull this member.

    for id 333 is having only 2 events in 2 week period, i.e; rannks 1,2 are in 2 weeks but rank 3 is having event date '2016-02-21 00:00:00.000' which is 2 months from ranks 1 and 2.so we should not need to pull this member.Let me know if is not clear.

    Thanks in advance. below is the sample data

    declare

    @events table

    (

    id int,

    eventdate datetime,

    ranks int

    )

    insert into @events values (111,'2016-09-30 00:00:00.000',1)

    insert into @events values (111,'2016-08-17 00:00:00.000',2)

    insert into @events values (111,'2016-08-15 00:00:00.000',3)

    insert into @events values (111,'2016-08-11 00:00:00.000',4)

    insert into @events values (222,'2015-04-17 00:00:00.000',1)

    insert into @events values (222,'2015-04-13 00:00:00.000',2)

    insert into @events values (222,'2015-04-13 00:00:00.000',3)

    insert into @events values (222,'2015-04-13 00:00:00.000',4)

    insert into @events values (222,'2015-04-10 00:00:00.000',5)

    insert into @events values (333,'2016-04-20 00:00:00.000',1)

    insert into @events values (333,'2016-04-05 00:00:00.000',2)

    insert into @events values (333,'2016-02-21 00:00:00.000',3)

    Select * From @events

    order by 1,3

    Maybe something like this could work:

    WITH CTE AS(
      Select *,
       CASE WHEN DATEADD( wk, -2, eventdate) <= LAG( eventdate, 2) OVER(PARTITION BY id ORDER BY eventdate)
        THEN 1 ELSE 0 END AS ThreeOrMoreEvents
      From @events
    )
    SELECT DISTINCT id
    FROM CTE
    WHERE ThreeOrMoreEvents = 1;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2