Avoid a loop in SQL - Tag the earliest event for a customer at least 30 days after the last tagged event

user_8185947 0
Viewed 42

One of our measures depends on identifying the first person to contact the client at least 30 days after the last "tagged" contact, where the first contact is always tagged. For example, we first gain a client on July 1st - that staff member gets a contact "point". Then, the customer rings up on July 10th and speaks to a different staff member - they do not get a contact "point". The customer rings on September 1st (62 days after the last tagged contact on July 1st) and speak to a third staff member - they get a contact point. Finally, they speak to a fourth person on September 20th - that fourth person does not gain a point.

We implement this is SQL Server using a loop:

  1. Tag the first event
  2. Find all events at least 30 days after the latest tagged event and "score" the earliest
  3. Repeat step 2 until there are no more events found

I would love to remove this loop because it is very slow, reading the table again and again, and in general I'd prefer set-based operations rather than looping. I was hoping someone has solved this already and could give some advice.

Here's what is looks like now:

DROP TABLE IF EXISTS #CUSTOMER_CONTACT
--Set up our table with the contact dates of each customer
--This is the "live" table in our system
CREATE TABLE #CUSTOMER_CONTACT (
                CUSTOMER_CODE       INT NOT NULL,
                EVENT_DATE          DATE NOT NULL,
                CONTACT_STAFF_CODE  VARCHAR(20) NOT NULL,
                FIRST_CONTACT_IN_30 INT NOT NULL,
                CONSTRAINT PK_#CUSTOMER_CONTACT PRIMARY KEY CLUSTERED ( CUSTOMER_CODE, EVENT_DATE )
                )

--Insert some dummy data for one customer over a few years
INSERT INTO #CUSTOMER_CONTACT( CUSTOMER_CODE, EVENT_DATE, CONTACT_STAFF_CODE, FIRST_CONTACT_IN_30 )
VALUES  ( 1, '2014-10-07', 'A', 0 ), 
        ( 1, '2014-12-01', 'A', 0 ), 
        ( 1, '2015-10-06', 'B', 0 ), 
        ( 1, '2015-10-07', 'C', 0 ), 
        ( 1, '2015-10-15', 'C', 0 ), 
        ( 1, '2015-11-10', 'C', 0 ), 
        ( 1, '2016-03-15', 'D', 0 ), 
        ( 1, '2016-03-18', 'A', 0 ), 
        ( 1, '2016-05-02', 'D', 0 ), 
        ( 1, '2016-09-09', 'D', 0 ), 
        ( 1, '2017-10-09', 'C', 0 ), 
        ( 1, '2017-10-10', 'B', 0 ), 
        ( 1, '2017-10-11', 'B', 0 ), 
        ( 1, '2017-10-20', 'D', 0 ), 
        ( 1, '2018-10-10', 'D', 0 ), 
        ( 1, '2019-03-07', 'D', 0 ), 
        ( 1, '2019-06-12', 'A', 0 ), 
        ( 1, '2019-08-02', 'A', 0 )

--Tag the very first contact per client so the staff person "A" gets a "point"
UPDATE  C
SET     C.FIRST_CONTACT_IN_30 = 1
FROM    #CUSTOMER_CONTACT AS C
            INNER JOIN  (
                        SELECT      CUSTOMER_CODE,
                                    MIN( EVENT_DATE ) AS FIRST_EVENT_DATE
                        FROM        #CUSTOMER_CONTACT
                        GROUP BY    CUSTOMER_CODE
                        ) AS M
                ON          M.CUSTOMER_CODE = C.CUSTOMER_CODE
                AND         M.FIRST_EVENT_DATE = C.EVENT_DATE

DROP TABLE IF EXISTS #NEXT_CONTACT_DATE
--This temp table will store the next event to be tagged
CREATE TABLE #NEXT_CONTACT_DATE (
                CUSTOMER_CODE   INT NOT NULL,
                EVENT_DATE      DATE NOT NULL,
                CONSTRAINT PK_#NEXT_CONTACT_DATE PRIMARY KEY CLUSTERED ( CUSTOMER_CODE, EVENT_DATE )
                )

--Just in case there is a flaw in my logic for the WHILE I'll use this to cap the iterations
DECLARE @I INT = 1

--For each customer, as long as I can find any record at least 30 days after the latest tagged records, keep
--on looping
WHILE EXISTS    (
                SELECT  *
                FROM    #CUSTOMER_CONTACT AS CC
                            INNER JOIN  (
                                        --Find the latest tagged event
                                        SELECT      CUSTOMER_CODE,
                                                    MAX( EVENT_DATE ) AS LATEST_CONTACT_DATE
                                        FROM        #CUSTOMER_CONTACT
                                        WHERE       FIRST_CONTACT_IN_30 = 1
                                        GROUP BY    CUSTOMER_CODE
                                        ) AS L
                                ON          L.CUSTOMER_CODE = CC.CUSTOMER_CODE
                                --Find any event at least 30 days after that
                                AND         DATEADD( DAY, 30, L.LATEST_CONTACT_DATE ) <= CC.EVENT_DATE
                ) AND
                --And make sure I haven't hit my iteration limit
                @I <= 100
BEGIN

    TRUNCATE TABLE #NEXT_CONTACT_DATE
    --Now store the earliest contact date for each customer that was at least 30 days
    --after the latest tagged event date
    INSERT INTO #NEXT_CONTACT_DATE( CUSTOMER_CODE, EVENT_DATE )
    SELECT      CC.CUSTOMER_CODE,
                MIN( CC.EVENT_DATE ) AS EVENT_DATE
    FROM        #CUSTOMER_CONTACT AS CC
                    INNER JOIN  (
                                SELECT      CUSTOMER_CODE,
                                            MAX( EVENT_DATE ) AS LATEST_CONTACT_DATE
                                FROM        #CUSTOMER_CONTACT
                                WHERE       FIRST_CONTACT_IN_30 = 1
                                GROUP BY    CUSTOMER_CODE
                                ) AS L
                        ON          L.CUSTOMER_CODE = CC.CUSTOMER_CODE
                        AND         DATEADD( DAY, 30, L.LATEST_CONTACT_DATE ) <= CC.EVENT_DATE
    GROUP BY    CC.CUSTOMER_CODE

    --Update the main table to tag that contact date to give a "point" to that staff member
    UPDATE  CC
    SET     CC.FIRST_CONTACT_IN_30 = 1
    FROM    #CUSTOMER_CONTACT AS CC
                INNER JOIN  #NEXT_CONTACT_DATE AS N
                    ON          N.CUSTOMER_CODE = CC.CUSTOMER_CODE
                    AND         N.EVENT_DATE = CC.EVENT_DATE

    --Don't forget to increment so my loop isn't endless
    SET @I = @I + 1

END

--Check the data at the end.
--It looks good to me
SELECT      CC.CUSTOMER_CODE,
            CC.EVENT_DATE,
            CC.CONTACT_STAFF_CODE,
            CC.FIRST_CONTACT_IN_30
FROM        #CUSTOMER_CONTACT AS CC

The below table shows the results. You can see that each of the first 3 events is tagged as they are all 30 days apart, but the 4th event is less than 30 days after the 3rd event. Event 5 is still within 30 days of event 3, but event 6 is now more than 30 days after the latest tagged event, so it is also tagged.

CUSTOMER_CODE   EVENT_DATE  CONTACT_STAFF_CODE  FIRST_CONTACT_IN_30
1               07/10/2014  A                   1
1               01/12/2014  A                   1
1               06/10/2015  B                   1
1               07/10/2015  C                   0
1               15/10/2015  C                   0
1               10/11/2015  C                   1
1               15/03/2016  D                   1
1               18/03/2016  A                   0
1               02/05/2016  D                   1
1               09/09/2016  D                   1
1               09/10/2017  C                   1
1               10/10/2017  B                   0
1               11/10/2017  B                   0
1               20/10/2017  D                   0
1               10/10/2018  D                   1
1               07/03/2019  D                   1
1               12/06/2019  A                   1
1               02/08/2019  A                   1

You can assume that there are no duplicate contact dates for one customer so I don't need to worry about two rows being flagged.

I haven't gotten anywhere with taking this out of the loop. I keep thinking there's something clever with ROW_NUMBER, or some cumulative SUM, but I get stuck on the fact that the counter needs to reset each time an event is tagged, which might 31 days after the last tagged one, or 365 days.

sql-server sql
1 Answers

This logic requires looping. It would not require looping if you were simply looking for a quiet period of 30 days. But consider the complications if there is an event every day.

You can do the looping inside the database using a recursive CTE. This should be much faster than using multiple queries externally. Here is what the code looks like:

with cs as (
      select cc.*, row_number() over (partition by customer_code order by event_date) as seqnum
      from customer_contact cc
     ),
     cte as (
      select customer_code, event_date, contact_staff_code, seqnum, 1 as flag
      from cs
      where seqnum = 1
      union all
      select cte.customer_code, cs.event_date, cs.contact_staff_code, cs.seqnum,
             (case when cs.event_date > dateadd(day, 30, cte.event_date) then 1 else 0 end)
      from cte join
           cs
           on cs.customer_code = cte.customer_code and cs.seqnum = cte.seqnum + 1
     )
select *
from cte
order by customer_code, event_date;

Here is a db<>fiddle.

user_11440350
Related