Surely there is a better way. - 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)

Surely there is a better way.

Author

Topic

ddawg
Starting Member

2 Posts

Posted - 2010-10-22 : 13:01:47

Hello folks,

I've used the info on this site for a while now but I've recently created what appears to be an overly complicated SQL statement and wonder if there isn't a better way to achieve the results.

The SQL works, however it seems overly complicated (note I've had to rename the columns and data in the example as the sensitive nature of the metadata and data require me to).

Situation
***********************
1. I need to pull the total of articles, books and audio books from a repository for each author.
2. I then need to rank the various objects by author and then assign a weighted point value to each one (ie lowest rank gets 2 pts, then 4, 6, 8...etc).
3. I then need to total all the point values for each author and rank all the authors by their overall point totals.

Data
************************

Author mags rnk pts book rnk pts a_book rnk pts total t_rnk
Bob 6 1 6 3 3 2 8 1 5 13 1
Jim 4 2 4 8 1 6 1 3 2 12 2
Luke 3 3 2 5 2 4 8 1 5 11 3

Here is my sql. It seems overly complicated but the main problem was trying to add (c_mags+c_books+c_a_book) as it would give me an error. The only way I could do it was to perform the aggregation in a select statment outside of the one that is creating the values.

Thoughts??

SQL
****************************

select
y.auth_name2 as o_n,
y.MAGS2 as MAGS,
y.Ranked_MAGS2 as rMAGS,
y.C_MAGS as cMAGS,
y.Books2 as Books,
y.Ranked_Books2 as rBooks,
y.C_Books as cBooks,
y.aud_book2 as aud_book,
y.Ranked_aud_book2 as raud_book,
y.C_aud_book as caud_book,
y.c_MAGS+y.c_Books+y.c_aud_book as tot,
rank() over (order by (y.c_MAGS+y.c_Books+y.c_aud_book)desc) as ov_rank

from
(
select
x.auth_Name as auth_name2,
sum(x.MAGS)as MAGS2,
x.Ranked_MAGS as Ranked_MAGS2,
(x.Ranked_MAGS *2)+(count(x.Ranked_MAGS)OVER (partition by x.Ranked_MAGS)-1) AS C_MAGS,
sum(x.Books)as Books2,
x.Ranked_Books as Ranked_Books2,
(x.Ranked_Books *2)+(count(x.Ranked_Books)OVER (partition by x.Ranked_Books)-1) AS C_Books,
sum(x.aud_book)as aud_book2,
x.Ranked_aud_book as Ranked_aud_book2,
(x.Ranked_aud_book *2)+(count(x.Ranked_aud_book)OVER (partition by x.Ranked_aud_book)-1) AS C_aud_book

from(
select
auth_IDS.auth_Name AS auth_Name,
objtype_totals3.auth_id AS auth_id,
objtype_totals3.Position AS Position3,
sum(objtype_totals3.MAGS) AS MAGS,
(RANK() OVER (order by sum(objtype_totals3.MAGS))) AS RANKED_MAGS,
sum(objtype_totals3.Books) AS Books,
(RANK() OVER (order by sum(objtype_totals3.Books))) AS RANKED_Books,
sum(objtype_totals3.aud_book) AS aud_book,
(RANK() OVER (order by sum(objtype_totals3.aud_book))) AS RANKED_aud_book

from
pool.dbo.auth_IDS auth_IDS,
pool.dbo.objtype_totals objtype_totals3
where
objtype_totals3.Position = 'F'
and
auth_IDS.auth_ID = objtype_totals3.auth_id
group by
auth_IDS.auth_Name,
objtype_totals3.auth_id,
objtype_totals3.Position
)x
group by auth_name,Ranked_MAGS, Ranked_Books, Ranked_aud_book
)y
group by auth_name2,MAGS2,Ranked_MAGS2,c_MAGS,Books2,Ranked_Books2,c_Books,aud_book2,Ranked_aud_book2,c_aud_book

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-23 : 02:23:49

how is data present in your source table? i assume what you've posted is your desired output as it already contains rank and points

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ddawg
Starting Member

2 Posts

Posted - 2010-10-24 : 21:47:52

quote:
Originally posted by visakh16

how is data present in your source table? i assume what you've posted is your desired output as it already contains rank and points

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

In my datasource the only data I would have (taking my example data) would be the author, the type (ie article, book, or audio book) and the number of each. The rank and point weighting is created in my sql within the two nested select statements.

It works, and against the 2000 or so rows it runs very efficiently. My concern isn't whether it works, but is this the best way to do it. I may be overlooking a situation which would cause problems with the rankings or points and while it doesn't sound like a big issue, for the actual data being off on 1 rank or having the point out by even 1 can cause huge problems and cost millions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources