Flag rows with 3 visits in the last 30 days

[david777]

Hi all.

I have a problem to  difficult to solve for me so i post my question here and hope that someone here can help me.

I have the three first columns in the table below.

I want to flag a visit with 1 if the person have had two more visits in the last 30 days.

The column Flag3VisitsLast30days is my desired output.

PersonID	VisitID	Visitdate	Flag3VisitsLast30days
1	1	2018-10-30	0
1	2	2018-10-01	1
1	3	2018-09-20	0
1	4	2018-09-15	0
1	5	2017-18-20	0
1	6	2016-05-10	0
1	7	2016-04-06	0
1	8	2015-01-20	0
2	9	2018-10-10	0
2	10	2017-09-08	0
2	11	2017-08-30	0
2	12	2016-05-05	0
2	13	2016-04-03	0
3	14	2018-09-01	0
3	15	2018-07-07	1
3	16	2018-06-20	0
3	17	2018-06-10	0
3	18	2017-11-15	0
3	19	2017-09-05	0
3	20	2017-08-01	0

Can anybody help me with this?

Thanks in advance.

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD * INLINE [

    PersonID, VisitID, Visitdate

    1, 1, 2018-10-30

    1, 2, 2018-10-01

    1, 3, 2018-09-20

    1, 4, 2018-09-15

    1, 5, 2017-08-20

    1, 6, 2016-05-10

    1, 7, 2016-04-06

    1, 8, 2015-01-20

    2, 9, 2018-10-10

    2, 10, 2017-09-08

    2, 11, 2017-08-30

    2, 12, 2016-05-05

    2, 13, 2016-04-03

    3, 14, 2018-09-01

    3, 15, 2018-07-07

    3, 16, 2018-06-20

    3, 17, 2018-06-10

    3, 18, 2017-11-15

    3, 19, 2017-09-05

    3, 20, 2017-08-01

];

FinalTable:

LOAD *,

If(PersonID = Previous(PersonID),

If(Visitdate - Previous(Visitdate) <= 30,

If(Visitdate - Previous(Previous(Visitdate)) <= 30, 1, 0), 0), 0) as Flag3VisitsLast30days

Resident Table

Order By PersonID, Visitdate;

DROP Table Table;