Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to get previous term

Author  Topic 

tanu
Yak Posting Veteran

57 Posts

Posted - 2007-09-14 : 09:55:11
Hi,

I need to write a report where I need to find out previous term for all the students for the college.
example

StudentID TermID
1 98
1 99
1 100
1 102
2 100
2 101
2 102
3 97
3 98
3 102

I want
StudentID TermID
1 100
2 101
3 98

Thanks.

Kristen
Test

22859 Posts

Posted - 2007-09-14 : 10:01:24
Can you explain why your sample output is:

1 100

rather than either

1 98
1 99
or
1 102

??

thanks

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 10:04:14
You mean you want second last record?

SELECT StudentID, TermID FROM (
SELECT StudentID, TermID, ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY TermID DESC) AS RecID FROM Table1
) AS d WHERE RecID = 2



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tanu
Yak Posting Veteran

57 Posts

Posted - 2007-09-14 : 10:17:18
Yes, I need second last record.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-09-14 : 10:20:16
So is Peso's code giving you the answer you want?

kristen
Go to Top of Page

tanu
Yak Posting Veteran

57 Posts

Posted - 2007-09-14 : 10:33:11
I am sorry I have more than one record for a term with different courses. I need the same result as previous.
That means I don't need second last record but I need second last term.

ex.

StudentID TermID CourseID
1 98 40
1 99 41
1 100 43
1 102 42
1 102 45
2 100 41
2 101 42
2 101 43
2 102 44
3 97 41
3 98 42
3 102 43
3 102 54

I want

I want
StudentID TermID
1 100
2 101
3 98
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 10:35:21
Try to run my query again, but before you do, change the "2" to a "3"...



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 10:52:37
Or this, depending of what you really want
-- Prepare sample data
DECLARE @Sample TABLE (StudentID INT, TermID INT, CourseID INT)

INSERT @Sample
SELECT 1, 98, 40 UNION ALL
SELECT 1, 99, 41 UNION ALL
SELECT 1, 100, 43 UNION ALL
SELECT 1, 102, 42 UNION ALL
SELECT 1, 102, 45 UNION ALL
SELECT 2, 100, 41 UNION ALL
SELECT 2, 101, 42 UNION ALL
SELECT 2, 101, 43 UNION ALL
SELECT 2, 102, 44 UNION ALL
SELECT 3, 97, 41 UNION ALL
SELECT 3, 98, 42 UNION ALL
SELECT 3, 102, 43 UNION ALL
SELECT 3, 102, 54

-- Show the expected output
SELECT DISTINCT StudentID,
TermID
FROM (
SELECT StudentID,
TermID,
DENSE_RANK() OVER (PARTITION BY StudentID ORDER BY TermID DESC) AS RecID
FROM @Sample
) AS d
WHERE RecID = 2


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tanu
Yak Posting Veteran

57 Posts

Posted - 2007-09-14 : 10:56:10
You are right but this was just an example.
Student may register for 1-4 courses in a given term so that query might not work in all situations.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 10:59:25
See edited response above, now with sample data!



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tanu
Yak Posting Veteran

57 Posts

Posted - 2007-09-14 : 11:36:07
This works. Thanks a lot.
Go to Top of Page
   

- Advertisement -