SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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 )
18 rows affected

select *
from customer_contact
order by customer_code, event_date


CUSTOMER_CODE
EVENT_DATE
CONTACT_STAFF_CODE
FIRST_CONTACT_IN_30


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


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


customer_code
event_date
contact_staff_code
seqnum
flag


1
2014-10-07
A
1
1

1
2014-12-01
A
2
1

1
2015-10-06
B
3
1

1
2015-10-07
C
4
0

1
2015-10-15
C
5
0

1
2015-11-10
C
6
0

1
2016-03-15
D
7
1

1
2016-03-18
A
8
0

1
2016-05-02
D
9
1

1
2016-09-09
D
10
1

1
2017-10-09
C
11
1

1
2017-10-10
B
12
0

1
2017-10-11
B
13
0

1
2017-10-20
D
14
0

1
2018-10-10
D
15
1

1
2019-03-07
D
16
1

1
2019-06-12
A
17
1

1
2019-08-02
A
18
1

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