Select statement - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Select statement

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-05-17 : 01:45:25

Hi,

I need to write a select statement that will return the current user's level and also what his level was before the new level. The user can have more than 2 entries in the database table and I want the report to show his current level and the one prior to the new entry.
I am not sure how to write such a query.

The query that I came up with returns the latest level:
I am joining ls table as that is where the student's personal details is stored.Committees is where the levels are stored.

select ls.sid,Committees.level
from learningstuds ls
JOIN Committees ON (ls.SID = Committees.SID)
AND (((Committees.YearCode = 26) AND (Committees.Level = 1)))

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-17 : 02:00:47

collie
Can you post table structures, sample table data and expected output

Andy

Beauty is in the eyes of the beerholder

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-05-17 : 02:42:58

Committees table structure (student can have more than one entry):
vid=int identity
sid=int (student's id linked to sid in learningstuds table)
yearcode=smalldatetime (year the level was assigned)
level =tinyint
sample data:

vid sid yearcode level
1 123 25 2
2 123 26 3
3 123 26 1
4 122 26 1

learningstuds table:
sid int identity
firstname
lastname
sample data:
sid Firstname Lastname
122 kevin Black
123 collie K

EXPECTED OUTPUT:
SID Firstname Lastname Yearcode Level Previous Level
122 kevin Black 26 1 -
123 collie K 26 1 3

(I tried to align the columns in my post but couldn't)

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-17 : 03:09:12

Try something like this


SELECT LS.SID, LS.FirstName, LS.LastName, C1.Yearcode, C1.[Level], C2.Previous
FROM LearningStuds LS 
	LEFT JOIN Comittees C1
		ON LS.SID = C1.SID
	LEFT JOIN (SELECT sid, MAX([Level]) AS Previous FROM Comittees
			WHERE Yearcode = 26 AND [Level] <> 1			
			GROUP BY SID) C2
		ON C1.SID = C2.SID
WHERE Yearcode = 26 AND [Level] = 1

Use the code tags to get additional formatting

Andy

Beauty is in the eyes of the beerholder

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-05-17 : 05:19:21

Hi,

Thanks.
The only problem is that it returns an entry where level=1 even if the last level wasn't 1 and i only need to return the students whose current level is 1 and then if they had a level prior to that then return the latest entry prior to the new entry.
eg
sid level
1 1
1 2

2 2
2 1

Then the output must only return sid=2 as it's latest level was 1 and it will display level=2 as the previous level.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-17 : 07:41:31

I think this will give you what you want


SELECT LS.SID, LS.FirstName, LS.LastName, C1.Yearcode, MIN(C1.[Level]) AS [Level], MIN(C2.Previous) AS Previous
FROM LearningStuds LS 
	LEFT JOIN Comittees C1
		ON LS.SID = C1.SID
	LEFT JOIN (SELECT C3.vid, C3.[Level] AS Previous
  			FROM Comittees C3 INNER JOIN 
						(SELECT sid, MIN([Level]) AS [Level] 
						FROM Comittees GROUP BY SID) C4 ON C3.SID = C4.SID
						WHERE C3.[Level] > C4.[Level]) C2
		ON C1.vid = C2.vid
WHERE Yearcode = 26 
GROUP BY LS.SID, LS.FirstName, LS.LastName, C1.Yearcode

Andy

Beauty is in the eyes of the beerholder

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-05-18 : 07:13:03

Thanks sooooooo much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources