My table in MS SQL looks like below:

CLIENT | CONTACT_DATE | WAY_CONTACT
-------------------------------------
123    | 2021-01-01   | phone
123    | 2021-01-10   | phone !
123    | 2021-01-11   | phone !
123    | 2021-04-05   | mail !
123    | 2021-04-06   | mail !
555    | 2021-11-02   | mail !
555    | 2021-11-03   | mail !
555    | 2021-11-05   | phone 

By "!" I signed moments where particular client has been cotacted more often than even 5 days by way_contact.

And I would like to calculate how many time a particular is situation where CONTACT_DATE is more often than every 5 days, so as a result I need something like below:

WAY_CONTACT | CONTACT_MORE_OFTEN_THAN_EVERY_5_DAYS
------------------------------------------------------
phone       | 1
mail        | 2

CodePudding user response：

Something like so:

select
    q.way_contact,
    sum(q.p)
from
    (select 
         t.way_contact,
         case 
             when datediff("d", 
                           lag(t.contact_date) over (partition by t.client, t.way_contact
                                                     order by t.client, t.way_contact, t.contact_date),
                           t.contact_date) <= 5 
                 then 1 else 0 
         end as p
     from 
         tblContact as t) as q
group by 
    q.way_contact
order by 
    q.way_contact

CodePudding user response：

Here's solution that uses a calculated rank for the within 5 days.

SELECT WAY_CONTACT
, SUM(CEILING(1.0*Days/5)) AS CONTACT_MORE_OFTEN_THAN_EVERY_5_DAYS
FROM
(
    SELECT CLIENT, WAY_CONTACT, Rnk
    , DATEDIFF(day, MIN(CONTACT_DATE), MAX(CONTACT_DATE)) AS Days
    FROM
    (
        SELECT *
        , [Rnk] = SUM(flag) OVER (PARTITION BY CLIENT, WAY_CONTACT ORDER BY CONTACT_DATE)
        FROM
        (
            SELECT *
            , [flag] = IIF(5 >= DATEDIFF(day, 
                LAG(CONTACT_DATE) OVER (PARTITION BY CLIENT, WAY_CONTACT ORDER BY CONTACT_DATE), 
                CONTACT_DATE), 0, 1)
            FROM CLIENT_COMMUNICATIONS
        ) q1
    ) q2
    GROUP BY CLIENT, WAY_CONTACT, Rnk
    HAVING MIN(CONTACT_DATE) < MAX(CONTACT_DATE)
) q3
GROUP BY WAY_CONTACT
ORDER BY WAY_CONTACT;

db<>fiddle here