How long it took to pass tests and to get to the highest level?

[aellistechsupport]

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.

1. how long it takes people to pass tests and
2. 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.

[aellistechsupport]

I left out that there is more than Senior as the highest level.

There are 3 levels.

[aellistechsupport]

How do I write

When the Name is the same, and the TestLevelID = 6 or 7 or 20, for each SkateTestTypeID, calculate how long it took from the first test to the last date (TestDate)
This is to calculate the time it took to the person to get to the highest test level.

[aellistechsupport]

I did find some info and got this far:

SELECT CompetitionsTests.Athlcete, CompetitionsTests.SkateTestTypeId, CompetitionsTests.TestType, Min(CompetitionsTests.[TestDate]) AS FirstTestDate, Max(CompetitionsTests.TestDate) AS LastTestDate, [LastTestDate]-[FirstTestDate] AS Diff
FROM CompetitionsTests
GROUP BY CompetitionsTests.Athlete, CompetitionsTests.SkateTestTypeId, CompetitionsTests.TestType
ORDER BY CompetitionsTests.Athlete, CompetitionsTests.SkateTestTypeId, Min(CompetitionsTests.[TestDate]);

I'll be working with this for the other parts but if anyone else is looking for something like this