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.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2003-07-31 : 15:34:30
|
| A delimma arrived today. It walked in right through my open back door and immediately stormed into my office and has me on it's horns.I have a table CourseRecords which holds course completion records for all users, any course.Trouble is it's possible for a user to take more than 1 course that corresponds to a specific credit. So there may be more than 1 record per user (this is bad for counting purposes).I need to efficently generate a recordset from this table that has 1 record per user for a course that falls into a credit group. This'll enable me to easily find users who've met a requirement and those who have not.This'll select all records that fall into a "course credit group"SELECT *FROM CourseRecordsWHERE CourseID IN (SELECT CourseID FROM dbo.UDF_CreditGroup(@CourseID)))Let's pause here. That UDF returns the recordset of CourseIDs that have the same credit. It ought to be efficent. I am guessing the optimizer evaluates it once, and the recordset is used in a JOIN to select the records from CourseRecords....Problem is it's possible for a user to take more than 1 course. The requirement is to return only the record that has the earliest finish-date . So we want the record with the minimum for column "FinishDate".Now the query gets knarley.SELECT A.* FROM ( SELECT * FROM dbo.CourseRecords CR WHERE CR.UserID = @UserID AND CR.CourseID IN (SELECT CourseID FROM dbo.UDF_CreditGroup(@CourseID)) ) A INNER JOIN ( SELECT UserID, MIn(CRecordFinish) as CRecordFinish FROM dbo.CourseRecords CR WHERE CR.UserID = @UserID AND CR.CourseID IN (SELECT CourseID FROM dbo.UDF_CreditGroup(@CourseID)) GROUP BY UserID ) B ON B.UserID = A.UserID AND B.CRecordFinish = A.CRecordFinishIt's a lot of writing. Is there a shorter technique or more efficent technique. How about using IN or EXISITS instead of the INNER JOIN??Maybe I ought to can this query in a VIEW and be done with it??Suggestions ??Sam |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-31 : 15:52:19
|
San,What's wrong with that?I'm wondering why though you have the UDF check in derived table ADoes that mean you can have more than 1 course completion on the same day.That would mean you would get n rows, not 1 then.What does show plan tell you? I;m thinking Index scan on the right and Index seek on the left, no?And what exactly doesquote:
more than 1 course that corresponds to a specific credit
Mean?Brett8-)SELECT POST=NewId() |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-07-31 : 19:28:14
|
Hey Brett,Maybe nothing's wrong with it. I'm going to throw it into a view and call it a day. quote:
And what exactly doesquote:more than 1 course that corresponds to a specific creditMean?
It's hidden in the UDF - Several courses can supply the same course credit. The UDF returns a recordset of all CourseIDs that have the same course credit as the CourseID passed as a parameter to the UDF.Cowabunga dude,Sam |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-08-01 : 09:23:43
|
| Do you mean corse credit like a course is 3 credits or 4 credits?If so, what doe sthat have to do with anything?If notWhat is a course credit...If soThen doesn't that happen a lot?Man I really have to go play golf...Brett8-)SELECT POST=NewId() |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-08-02 : 14:46:24
|
| Course Credit Group is a term used to describe a set of courses all of which have the same "credit". In other words, take any of these courses, your pick, it doesn't matter.The UDF takes any CourseID, and returns a recordset of CourseIDs that all supply the same credit.Sam |
|
|
|
|
|
|
|
|
Hate TOP 1 ?