Ok, not sure how to accomplish this ...
How long it took to pass tests and to get to the highest level?
Sometimes it'll be on the first try, other times a few times before passing tests.
AND from prelim up to senior level.
- how long it takes people to pass tests and
- how long it took to get from prelim level to senior level for each type of test
Here's a sampling of the data set:
Athlete SkateTestTypeId TestType TestCodeSeq TestName TestLevelId TestLevels TestDate Passed TestTrack Rank Completed PaymentDate Birthdate A. Johanna Bradfield 1 03-Dance 300 Dutch Waltz 18 Preliminary 2/28/2004 -1 Standard 10 -1 4/1/2004 10/9/1985 A. Johanna Bradfield 1 03-Dance 301 Canasta Tango 18 Preliminary 2/28/2004 -1 Standard 10 -1 4/1/2004 10/9/1985 A. Johanna Bradfield 1 03-Dance 302 Rhythm Blues 18 Preliminary 2/28/2004 -1 Standard 10 -1 4/1/2004 10/9/1985 A. Johanna Bradfield 1 03-Dance 303 Preliminary Dance Test 18 Preliminary 2/28/2004 -1 Standard 10 -1 4/1/2004 10/9/1985 A. Johanna Bradfield 4 01-Free Skating 100 Pre Preliminary Free Skating 16 Pre Preliminary 6/18/1998 -1 Standard 10 -1 9/23/1998 10/9/1985 A. Johanna Bradfield 4 01-Free Skating 105 Preliminary Free Skating 18 Preliminary 6/18/1998 -1 Standard 10 -1 9/23/1998 10/9/1985 A. Johanna Bradfield 4 01-Free Skating 110 Pre Juvenile Free Skating 15 Pre Juvenile 10/15/1998 -1 Standard 10 -1 2/24/1999 10/9/1985 A. Johanna Bradfield 4 01-Free Skating 120 Juvenile Free Skating 11 Juvenile 10/15/1998 -1 Standard 10 -1 2/24/1999 10/9/1985 A. Johanna Bradfield 4 01-Free Skating 130 Intermediate Free Skating 8 Intermediate 1/20/2000 -1 Standard 10 -1 3/24/2000 10/9/1985 A. Johanna Bradfield 4 01-Free Skating 140 Novice Free Skating 12 Novice 3/15/2001 -1 Standard 10 -1 5/25/2001 10/9/1985 A. Johanna Bradfield 4 01-Free Skating 150 Junior Free Skating 10 Junior 6/20/2002 -1 Standard 10 -1 9/4/2002 10/9/1985 A. Johanna Bradfield 5 00-Moves 10 Pre Preliminary Moves In The Field 16 Pre Preliminary 6/18/1998 -1 Standard 10 -1 9/23/1998 10/9/1985 A. Johanna Bradfield 5 00-Moves 11 Preliminary Moves In The Field 18 Preliminary 6/18/1998 -1 Standard 10 -1 9/23/1998 10/9/1985 A. Johanna Bradfield 5 00-Moves 12 Pre Juvenile Moves In The Field 15 Pre Juvenile 10/15/1998 -1 Standard 10 -1 2/24/1999 10/9/1985 A. Johanna Bradfield 5 00-Moves 13 Juvenile Moves In the Field 11 Juvenile 10/15/1998 -1 Standard 10 -1 2/24/1999 10/9/1985 A. Johanna Bradfield 5 00-Moves 14 Intermediate Moves In The Field 8 Intermediate 3/18/1999 -1 Standard 10 -1 6/23/1999 10/9/1985 A. Johanna Bradfield 5 00-Moves 15 Novice Moves In The Field 12 Novice 1/18/2001 -1 Standard 10 -1 4/10/2001 10/9/1985 A. Johanna Bradfield 5 00-Moves 16 Junior Moves In The Field 10 Junior 11/15/2001 0 Standard 10 -1 2/25/2002 10/9/1985 A. Johanna Bradfield 5 00-Moves 16 Junior Moves In The Field 10 Junior 3/21/2002 -1 Standard 10 -1 5/10/2002 10/9/1985 A. Johanna Bradfield 5 00-Moves 17 Senior Moves In The Field 20 Senior 2/28/2004 0 Standard 10 -1 4/1/2004 10/9/1985 A. Johanna Bradfield 5 00-Moves 17 Senior Moves In The Field 20 Senior 12/19/2004 -1 Standard 10 -1 12/19/2004 10/9/1985
So for this person took 3 types of tests.
and finally ending by getting to Senior level, which was taken twice before passing. Just as the Junior level was also taken twice.
I need to figure out how to get the time lapsed from the very first date from each test type to the last where the person finally passed.
Let's look at test type 5 "00-Moves" it started from Prelim to Senior (as originally noted trying to see how long that took)
1. Passed Senior Level on 12/19/2004 from the 1st Senior Level test on 4/1/2004 (note they could have taken more times, like 3 or 4, etc ... so it has to be the time it took from first and the last)
2. The other part is the very first test in the test type 5 "00-Moves" was originally taken on 9/23/1998 and it wasn't until 12/19/2004 when this person passed it, although got to senior level on 4/1/2004. They aren't officially a Senior level until the test is passed.
For the other test types it'll look at the person and when there's a change on person and a change on test type look at the last date and the first date to calculate the time it took.
BUT when the test type gets to the Senior level it needs ALSO look at how long it took to pass the Senior level test.
So not sure how to get the two formulas. Help would be appreciated.