Identify and flag the repetitive daily-ordering pattern

[ajipaul03]

Hello Experts,

I have a complex requirement to identify and flag the repetitive daily-ordering pattern.

Please see the sample data below.

The intend is to create the following fields as show in the table below

REPEAT_BATCH_ID
REPEAT_SEQ
FIRST_REPEAT

Any thoughts?

Thanks,

Aji

REC_ID	CUST	PROD	DT		REPEAT_BATCH_ID	REPEAT_SEQ	FIRST_REPEAT
1	C1	ABC	6/01/2019
2	C1	HIJ	6/01/2019
3	C1	XYZ	1/01/2019		1	1	First
4	C1	XYZ	2/01/2019		1	2	Repeat
5	C1	LMN	4/01/2019
6	C1	XYZ	5/01/2019		2	1	First
7	C1	XYZ	6/01/2019		2	2	Repeat
8	C1	XYZ	7/01/2019		2	3	Repeat
9	C1	XYZ	8/01/2019		2	4	Repeat
10	C1	XYZ	18/01/2019
11	C2	ABC	8/01/2019		3	1	First
12	C2	ABC	9/01/2019		3	2	Repeat
13	C2	HIJ	1/01/2019
14	C2	HIJ	3/01/2019		4	1	First
15	C2	HIJ	4/01/2019		4	2	Repeat
16	C2	HIJ	12/02/2019
17	C2	XYZ	3/01/2019		5	1	First
18	C2	XYZ	4/01/2019		5	2	Repeat
19	C2	XYZ	5/01/2019		5	3	Repeat
20	C2	XYZ	12/02/2019

[Arthur_Fong · Accepted solution]

There you go:

Is a combination of both front end and back end script to do this.

Do have a look on the logic to build this.

Thanks and regards,

Arthur Fong

[ajipaul03]

Hi Arthur,

Brilliant!!!

Couple of minor changes I did
the actual REC_ID is alphanumeric. so created a new filed using rowno() .
Used autonumber with Repeat_Batch_ID to generate the REPEAT_SEQ.

Thanks for your help!
Aji