How to approach? - 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 2005 Forums

Transact-SQL (2005)

How to approach?

Author

Topic

akpikes
Starting Member

1 Post

Posted - 2007-01-05 : 03:52:19

I having a problem with figuring out how to get the problem below accomplished.

Problem:

I have a table full of entries which contain community service events and how many hours were recorded, these entries are also identified by a member id, year, and semester.

I currently have a function which returns all of the events for a given year/semester combination.

SELECT TOP 100 PERCENT commservice_stunum, commservice_year, commservice_semester, SUM(commservice_hours) AS commservice_hour_sum
FROM dbo.commservice
GROUP BY commservice_stunum, commservice_year, commservice_semester
HAVING (commservice_year = @year) AND (commservice_semester = @semester)
ORDER BY commservice_year DESC, commservice_semester, SUM(commservice_hours) DESC

This seems to be working fine for me on the website...
However, on the website I need to know where each individual ranks compared to everyone else. So currently I am using a stored procedure (possibly incorrectly) to generate the same table as above, but with a rank.

SELECT TOP 100 PERCENT a1.commservice_stunum, a1.commservice_year, a1.commservice_semester, a1.commservice_hour_sum,COUNT(a2.commservice_hour_sum) AS commservice_rank
FROM dbo.commservice_hours_semester(@year, @semester) a1 CROSS JOIN dbo.commservice_hours_semester(@year, @semester) a2
WHERE (a1.commservice_hour_sum <= a2.commservice_hour_sum) OR (a1.commservice_hour_sum = a2.commservice_hour_sum) AND (a1.commservice_hour_sum = a2.commservice_hour_sum)
GROUP BY a1.commservice_stunum, a1.commservice_year, a1.commservice_semester, a1.commservice_hour_sum
ORDER BY a1.commservice_hour_sum DESC

Now, this seems to be working so far as well... but I can't reference this stored procedure inside another stored procedure, view, or function...

Possible Solution:

Would a function which returns a members rank given (id, semester, and year) be able to be used repeatedly within a stored procedure, and how would I write that function.

Hell, possibly there is a better way to do all of this. I appreciate any support.

Also, keep in mind I am referencing the information in the end via a website.

Thanks In Advance,

David

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-05 : 12:40:55

You can just put that second query in a view or another function. You didn't give the whole stored procedure, but if that query is the only thing in the stored procedure then there is no need for it to be a stored procedure.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources