Determine Days Since Last Submission 1

[SgtJarrow]

I have a database that tracks user's attempts at tests. I need to determine how many days have passed between each test. It should not be that hard, but I just can't seem to find the right combination needed to make it work. I have tried using ctes, sub-queries to get max dates, self-joins and I just can't get the data to match up.

I am pretty sure I could make this work by writing a cursor, but I am trying to avoid that. I basically need to find the "previous" record based on the current record. Previous in this case means the closest date to the date of the record and the Id being at least one less than the current record.

I am not asking for full code (unless you want to )...just a nudge in the right direction. JOIN, CTE, SUB-QUERY, Whatever method

Database is SQL 2008

Thank you.

DECLARE @StudentCourseWork TABLE
(
	PersonId			INT
	, ProgramId			INT
	, TestId			INT
	, AttemptDate		DATETIME
)

INSERT INTO @StudentCourseWork VALUES (1, 1, 1, '2013-10-11')
INSERT INTO @StudentCourseWork VALUES (1, 1, 2, '2013-10-12')
INSERT INTO @StudentCourseWork VALUES (1, 1, 3, '2013-10-14')
INSERT INTO @StudentCourseWork VALUES (1, 2, 1, '2013-10-15')
INSERT INTO @StudentCourseWork VALUES (1, 2, 2, '2013-10-20')
INSERT INTO @StudentCourseWork VALUES (2, 1, 1, '2013-10-11')
INSERT INTO @StudentCourseWork VALUES (2, 1, 2, '2013-10-13')
INSERT INTO @StudentCourseWork VALUES (2, 1, 3, '2013-10-13')
INSERT INTO @StudentCourseWork VALUES (2, 1, 4, '2013-10-14')
INSERT INTO @StudentCourseWork VALUES (3, 1, 1, '2013-10-08')
INSERT INTO @StudentCourseWork VALUES (3, 1, 2, '2013-10-09')
INSERT INTO @StudentCourseWork VALUES (3, 1, 3, '2013-10-10')
INSERT INTO @StudentCourseWork VALUES (3, 2, 1, '2013-10-13')
INSERT INTO @StudentCourseWork VALUES (3, 2, 2, '2013-10-22')
INSERT INTO @StudentCourseWork VALUES (3, 2, 3, '2013-10-26')

SELECT
	PersonId
	, ProgramId
	, TestId
	, AttemptDate
	, DATEDIFF(DAY, 'previous attempt date', AttemptDate) 'DaysSincePreviousAttempt'
FROM @StudentCourseWork

/*
Expected Output
PersonId		        ProgramId		        TestId				        AttemptDate		DaysSincePreviousAttempt
1				1				1					2013-10-11		NULL
1				1				2					2013-10-12		1
1				1				3					2013-10-14		2
1				2				1					2013-10-15		NULL
1				2				2					2013-10-20		5
2				1				1					2013-10-11		NULL
2				1				2					2013-10-13		2
2				1				3					2013-10-13		0
2				1				4					2013-10-14		1
3				1				1					2013-10-08		NULL
3				1				2					2013-10-09		1
3				1				3					2013-10-13		1
3				2				1					2013-10-13		NULL
3				2				2					2013-10-22		9
3				2				3					2013-10-26		4
*/

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[gmmastros]

I notice from your sample data that TestId appears to be a "counter" for each person/test combination. It appears to start at 1 and increments by 1.

Can this functionality be relied upon?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Either way, the "trick" here is to join the table back to itself. When you do this, you need to alias at least one of the tables (I prefer to alias them both).

If TestId is a true incrementing counter per person/program then you can do this:

Select  A.PersonId,
        A.ProgramId,
        A.TestId,
        A.AttemptDate,
        DateDiff(Day, B.AttemptDate, A.AttemptDate) As 'DaysSincePreviousAttempt'
From    @StudentCourseWork As A
        Left Join @StudentCourseWork As B
		  On A.PersonId = B.PersonId
		  And A.ProgramId = B.ProgramId
		  And A.TestId = B.TestId + 1

Notice the join clause. I join the table to itself based on personid, programid, and testid = testid + 1.

If you cannot rely on TestId being an incrementing counter, then you could introduce your own using the Row_Number() function, like this:

;With Data As
(
  Select  PersonId, 
          ProgramId, 
          AttemptDate, 
          TestId,
          Row_Number() Over (Partition By PersonId, ProgramId Order BY AttemptDate) As RowId
  From    @StudentCourseWork
)
Select  A.PersonId,
        A.ProgramId,
        A.TestId,
        A.AttemptDate,
        DateDiff(Day, B.AttemptDate, A.AttemptDate) As 'DaysSincePreviousAttempt'
From    Data As A
        Left Join Data As B
	  On A.PersonId = B.PersonId
	  And A.ProgramId = B.ProgramId
	  And A.RowId = B.RowId + 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SgtJarrow]

The "counter" is a seeded integer for each of the tables....but they are not garaunteed to be sequential. There will be gaps. Sorry if my example is not enough.

So person 1 may have programs 1, 4, 5, 6, 8 and the attempts may be for 1: 1, 4, 5, 7, for 2: 2, 6, 8, 9, and so on. But the id are unique per column.

Basically they are actually stored in separate relational tables Person, Program, and Test. The Attempt table just has the foreign keys back to the individual tables. I put them all into one table for this example because I can always load them into a temp as such, and the extract as needed.

If it will help, I can provide a second sample data set that would be all the tables and something a bit more realistic....Thanks.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[gmmastros]

In that case, ignore the first code block in my previous post and use the second one instead.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SgtJarrow]

Second example was spot on......Thank you so much.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[gmmastros]

You're welcome. Do you understand how it works? Would you like me to explain it?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SgtJarrow]

Thanks, but I don't need that. Again, I was looking for the nudge in the right direction. I was almost there, but your final join set:

from Data As A
Left Join Data As B
On A.PersonId = B.PersonId
And A.ProgramId = B.ProgramId
And A.RowId = B.RowId + 1

was the key I was missing. I had almost everything else in one of my attempts.



=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer