October 7, 2015 at 8:19 am
I have a dataset as such:
Student TestTypeDate TestCnt
111-22-1111English2015-09-01 10:00:00 1
111-22-1111Math2015-09-02 11:00:00 2
111-22-1111Geo2015-09-03 12:00:00 3
222-11-2222English2015-09-01 10:00:00 1
333-22-1111English2015-09-01 10:00:00 1
333-22-1111Math2015-09-02 11:00:00 2
444-11-2222English2015-09-01 10:00:00 1
555-11-2222English2015-09-01 10:00:00 1
555-11-2222Math2015-09-02 11:00:00 2
555-11-2222Geo2015-09-03 12:00:00 3
555-11-2222History2015-09-04 13:00:00 4
So some have just 1 test and some have multiple. I have a count for each.
What I need to do is use that count and get an average time between each test per student.
Any suggestions on an easy way to accomplish this is greatly appreciated!
October 7, 2015 at 8:29 am
woody_rd (10/7/2015)
I have a dataset as such:Student TestTypeDate TestCnt
111-22-1111English2015-09-01 10:00:00 1
111-22-1111Math2015-09-02 11:00:00 2
111-22-1111Geo2015-09-03 12:00:00 3
222-11-2222English2015-09-01 10:00:00 1
333-22-1111English2015-09-01 10:00:00 1
333-22-1111Math2015-09-02 11:00:00 2
444-11-2222English2015-09-01 10:00:00 1
555-11-2222English2015-09-01 10:00:00 1
555-11-2222Math2015-09-02 11:00:00 2
555-11-2222Geo2015-09-03 12:00:00 3
555-11-2222History2015-09-04 13:00:00 4
So some have just 1 test and some have multiple. I have a count for each.
What I need to do is use that count and get an average time between each test per student.
Any suggestions on an easy way to accomplish this is greatly appreciated!
Why do you need to use the count?
DROP TABLE #Sample
CREATE TABLE #Sample (Student VARCHAR(11), TestType VARCHAR(15), [Date] DATETIME, TestCnt INT)
INSERT INTO #Sample (Student, TestType, [Date], TestCnt)
SELECT '111-22-1111', 'English', '2015-09-01 10:00:00', 1 UNION ALL
SELECT '111-22-1111', 'Math', '2015-09-02 11:00:00', 2 UNION ALL
SELECT '111-22-1111', 'Geo', '2015-09-03 12:00:00', 3 UNION ALL
SELECT '222-11-2222', 'English', '2015-09-01 10:00:00', 1 UNION ALL
SELECT '333-22-1111', 'English', '2015-09-01 10:00:00', 1 UNION ALL
SELECT '333-22-1111', 'Math', '2015-09-02 11:00:00', 2 UNION ALL
SELECT '444-11-2222', 'English', '2015-09-01 10:00:00', 1 UNION ALL
SELECT '555-11-2222', 'English', '2015-09-01 10:00:00', 1 UNION ALL
SELECT '555-11-2222', 'Math', '2015-09-02 11:00:00', 2 UNION ALL
SELECT '555-11-2222', 'Geo', '2015-09-03 12:00:00', 3 UNION ALL
SELECT '555-11-2222', 'History', '2015-09-04 13:00:00', 4
SELECT
Student, TestType, [Date], TestCnt,
LAG([Date],1) OVER(PARTITION BY Student ORDER BY [Date])
FROM #Sample
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 7, 2015 at 8:37 am
Because I need an average of number of days between all tests. Not just the previous. So if a student took 10 tests, I need the average of the number of days between all 10 tests.
October 7, 2015 at 8:40 am
woody_rd (10/7/2015)
Because I need an average of number of days between all tests. Not just the previous. So if a student took 10 tests, I need the average of the number of days between all 10 tests.
The difference between the first exam datetime and the last exam datetime, divided by the number of exams?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 7, 2015 at 8:42 am
Wouldn't the more accurate number be the difference between each test (diff test1 date and test2 date, test2 and test3, ... test9 and test10), totaled and divided by number of tests?
October 7, 2015 at 8:46 am
woody_rd (10/7/2015)
Wouldn't the more accurate number be the difference between each test (diff test1 date and test2 date, test2 and test3, ... test9 and test10), totaled and divided by number of tests?
I think they would be the same - but if you want to do it this way, then you're back to using LAG. Here's what I had in mind for Plan B:
DROP TABLE #Sample
CREATE TABLE #Sample (Student VARCHAR(11), TestType VARCHAR(15), [Date] DATETIME, TestCnt INT)
INSERT INTO #Sample (Student, TestType, [Date], TestCnt)
SELECT '111-22-1111', 'English', '2015-09-01 10:00:00', 1 UNION ALL
SELECT '111-22-1111', 'Math', '2015-09-02 11:00:00', 2 UNION ALL
SELECT '111-22-1111', 'Geo', '2015-09-03 12:00:00', 3 UNION ALL
SELECT '222-11-2222', 'English', '2015-09-01 10:00:00', 1 UNION ALL
SELECT '333-22-1111', 'English', '2015-09-01 10:00:00', 1 UNION ALL
SELECT '333-22-1111', 'Math', '2015-09-02 11:00:00', 2 UNION ALL
SELECT '444-11-2222', 'English', '2015-09-01 10:00:00', 1 UNION ALL
SELECT '555-11-2222', 'English', '2015-09-01 10:00:00', 1 UNION ALL
SELECT '555-11-2222', 'Math', '2015-09-02 11:00:00', 2 UNION ALL
SELECT '555-11-2222', 'Geo', '2015-09-03 12:00:00', 3 UNION ALL
SELECT '555-11-2222', 'History', '2015-09-04 13:00:00', 4
SELECT
Student,
ExamStart = MIN([Date]),
ExamEnd = MAX([Date]),
ExamCount = COUNT(*)
FROM #Sample
GROUP BY Student
Note that I'm only taking you halfway to any given solution - this has to be homework.
Edit: scrub that, you've been around for a while.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 7, 2015 at 8:52 am
lol, yes, this is not homework, and the dataset is actually a little more complex than what I presented. I dumbed it down a little to get to the overall scope of what I needed to accomplish, just didn't know the terminology to get to a finished result. Was looking for a point in the right direction to get iteration steps between tests.
October 7, 2015 at 9:03 am
woody_rd (10/7/2015)
lol, yes, this is not homework, and the dataset is actually a little more complex than what I presented. I dumbed it down a little to get to the overall scope of what I needed to accomplish, just didn't know the terminology to get to a finished result. Was looking for a point in the right direction to get iteration steps between tests.
Sure no probs. This should get you started:
SELECT *, AverageMinutesBetweenExams = DATEDIFF(MINUTE,ExamStart,ExamEnd)/NULLIF(Examcount-1,0)
FROM (
SELECT
Student,
ExamStart = MIN([Date]),
ExamEnd = MAX([Date]),
ExamCount = COUNT(*)
FROM #Sample
GROUP BY Student
) d
SELECT Student, AverageMinutesBetweenExams = SUM(MinutesBetweenExams)/NULLIF(COUNT(*)-1,0)
FROM (
SELECT *, MinutesBetweenExams = DATEDIFF(MINUTE,l,[Date])
FROM (
SELECT
Student, TestType, [Date], TestCnt,
l = LAG([Date],1) OVER(PARTITION BY Student ORDER BY [Date])
FROM #Sample
) d
) e
GROUP BY Student
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 7, 2015 at 9:24 am
You don't really need a subquery here.
SELECT Student,
CAST(DATEDIFF(MINUTE, MIN([Date]), MAX([Date])) / NULLIF((COUNT(*) - 1), 0) / 60.0 AS decimal(9, 2)) AS Avg_Hours_Btwn_Tests
FROM #Sample
GROUP BY Student
ORDER BY Student
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 7, 2015 at 9:29 am
ScottPletcher (10/7/2015)
You don't really need asubqueryderived table here....
No, but they help clarify the solution. Like TSQL isinglass.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 7, 2015 at 9:31 am
Many thanks, guys! Both seemed to work for what I needed. Appreciate the help!
October 7, 2015 at 9:32 am
Many thanks, guys! Both seemed to work for what I needed!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply