[Calculated Column] Flag Repetitions considering Conditionals between rows (similar no Peek())

[Gustavo Soares]

Hello there,

I need to create a calculated column to indicate if a record was repeated in within an interval of 30 days, but considering some rules

Rules to flag as a repetition:

1. Same CPF;

2. DATE difference needs to be less than 30 days;

3. [LOCATION1, LOCATION2] pair needs to be equal beetwen records.

4. PROTOCOL needs to be different;

 

Example:

(ID, CPF, PROTOCOL, LOCATION1, LOCATION2, DATE)

(1 , '000', 123, 'A', 'AA', '2019-05-01'),

(2 , '000', 123, 'A', 'AA', '2019-05-02'), -- This one shouln't be flagged, becauseit doesn'tsatisfythe different protocols clause

(3 , '000', 124, 'B', 'AA', '2019-05-20') --This one shouln't be flagged, becauseit doesn'tsatisfythe Locations clause

(4 , '000', 121, 'A', 'AA', '2019-05-20'), -- This one needs to be flagged as a repetition, because it happened in less than 30 days, has the same [LOCATION1, LOCATION2], and different PROTOCOL when compared to record ID=1

(5 , '001', 121, 'A', 'AA', '2019-05-20'), -- Not flagged, different CPF

(6 , '001', 120, 'A', 'AA', '2019-05-25'), -- Flagged