Consecutive Test Dates with criteria

[PBOBOP]

Hello,

I would like to create a table like the one below to showcase students' test dates and scores. However, I am having trouble incorporating certain criteria so that the correct date is reflected.

This is the criteria:
1. Section A of the test must be attempted (have a score/not be empty) and the test score must be 70.

2. The latest test date and the second latest test date must be consecutive(2 tests in a row), the student should not have attempted any tests between these dates. 

Note: If they have 2 test attempts on the same day, as long as criteria 1 has been fulfilled for one of the tests, it can be considered.

Here is some sample data:

Student ID	Test Date	Section A Score	Test Score
232011	Sep 27, 2022		70
232011	Sep 27, 2022	22	70
232011	Sep 16, 2022
232011	Sep 13, 2022
232011	Sep 7, 2022
232011	Aug 31, 2022
232011	Aug 9, 2022
232011	Jul 29, 2022
232011	Jul 27, 2022		60
232011	Jul 14, 2022		70
232011	Jul 12, 2022
232011	Jul 12, 2022	16	70
232011	Jul 7, 2022	9	70
232011	Jul 4, 2022		70
232011	Jun 30, 2022		70
232033	Oct 31, 2022		70
232033	Oct 31, 2022	13	70
232033	Oct 27, 2022		70
232033	Oct 27, 2022	12	70
232033	Oct 20, 2022		70
232033	Oct 20, 2022		70
232261	Jan 30, 2024	28	60
232261	Jan 30, 2024	21	70
232261	Jan 25, 2024		60
232261	Jan 25, 2024		60
232261	Jan 22, 2024		70
232261	Jan 22, 2024	24	70
232261	Jan 18, 2024		60
232261	Jan 18, 2024	32	60
232261	Jan 15, 2024		70
232261	Jan 15, 2024	20	70
232261	Jan 11, 2024		70
232261	Jan 11, 2024	15	70
232261	Jan 8, 2024		70
232261	Jan 8, 2024	21	70
232261	Dec 21, 2023	25	60
232261	Dec 21, 2023	26	60
232261	Dec 18, 2023		70
232261	Dec 18, 2023	17	70
232261	Dec 14, 2023		70
232261	Dec 14, 2023	11	70
232261	Dec 11, 2023		70
232261	Dec 11, 2023	18	70
232261	Dec 4, 2023		60
232261	Dec 4, 2023	23	60
232261	Nov 30, 2023		60
232261	Nov 30, 2023		60
232261	Nov 27, 2023		70
232261	Nov 27, 2023	21	70

For student 232011, the "latest test date" should be Jul 12 2022 and "second latest test date" should be Jul 07 2022. 

The latest test that should be considered is on Sep 27 2022, student 232011 attempted section A and had a test score of 70.
The previous date that he took the test and attempted section A and had a test score of 70 is Jul 12 2022. However between Sep 27 2022 and Jul 12 2022, he had 8 other attempts (Sep 16, Sep 13, Sep 7, Aug 31, Aug 9, Jul 29, Jul 27, Jul 14) which do not count since he did not attempt Section A for any of these attempts.

Therefore, the test dates that are one after the other, where student 232011 attempted section A and had a test score of 70 are Jul 12 2022 and Jul 07 2022 as he did not attempt any other test between these dates.

For student 232033, the "latest test date" should be Oct 31 2022, and the "second latest test date" should be Oct 27 2022.

He attempted 2 tests on Oct 31 2022 and out of the 2 tests, one of them fulfilled criteria 1.

The next latest date he attempted a test which fulfilled criteria 1 is on Oct 27 2022. Since he did not have any other attempts between these 2 dates

For student 232261, he had 2 test attempts on Jan 30, one did not fulfil criteria 1, and the other fulfilled criteria 1. The next test that fulfils criteria 1 is on Jan 22, 2024. However this student had other test attempts between Jan 22 and Jan 30 (Jan 25). Therefore the tests on Jan 22 and Jan 30 that fulfil criteria one are not “consecutive” and do not fulfil criteria 2.

The next test before Jan 22 that fulfils criteria 1 was taken on Jan 15, 2024. However, between Jan 15 and Jan 22 he attempted a test on Jan 18 that did not fulfil criteria 1, so we cannot consider the test on Jan 22.

Prior to Jan 15, the latest test where he fulfilled criteria 1 was on Jan 11, 2024. There are also no tests attempted between Jan 15 and Jan 11, therefore these tests are considered “consecutive” and fulfil criteria 2.

Please help, thank you! 🙂

[lbendlin]

2. The latest test date and the second latest test date must be consecutive, the student should not have attempted any tests     between these dates. 

What is your definition of "consecutive"?

Your sample data seems to indicate very different dates than your explanation?

[PBOBOP]

Hi Ibendlin, 

My definition of consecutive means 2 tests in a row. I have added further explanation to my original post.