Help seems like very nasty query

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

SQL Server Development (2000)

Help seems like very nasty query

Author

Topic

ajay
Starting Member

34 Posts

Posted - 2002-11-22 : 04:24:54

Hello all,

My table structure is like this:
userid type techtype class

1 article vb recommended
1 article asp original
1 book vb recommended
1 tool c# original
1 tip vb recommended
1 tip vb original
1 tool csharp recommended
2
2
3
4
3
5
.
.
.
and so non.

There is one user table which has user information like:
userid name email ifvisible
1 abc test@test.com 0
2 def test1@test1.com 1
and so on

There are only four type i.e article,books,tips,tools

Now
For every original article I would give 100 points and for recommended I'd give 50 points.
For every original book I would give 30 points and for recommended I'd give 20 points.
For every original tool I would give 70 points and for recommended I'd give 45 points.
For every original tip I would give 80 points and for recommended I'd give 25 points.

I'd like to have a result like this

name article books tips tools total -->This row can be hard coded.

1 150 30 105 115 400

2

and so on....displaying order by total descending.

Also if ifvisible field is 1 i'd like to display username with a hyperlink which has his email as mailto: function.

Please help and tell me the query to get this...

truly,

ajay

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-22 : 04:33:04

Something like...

SELECT
userid,
sum (case when type = 'article' and class = 'original' then 100
when type = 'article' and class = 'recommended' then 50
else 0 end) as article,
sum (case when type = 'book' and class = 'original' then 30
when type = 'book' and class = 'recommended' then 20
else 0 end) as book,
sum (case when type = 'tool' and class = 'original' then 70
when type = 'tool' and class = 'recommended' then 45
else 0 end) as tool,
sum (case when type = 'tip' and class = 'original' then 80
when type = 'tip' and class = 'recommended' then 25
else 0 end) as tip,
case when ifvisible = 1 then email else null end as hyperlink
from thingstable t
inner join users u on
u.userid = t.userid
group by userid, hyperlink

Edited by - mr_mist on 11/22/2002 04:34:42

ajay
Starting Member

34 Posts

Posted - 2002-11-22 : 05:26:27

Thanks alot for responsding so fast.
However how to display totals also.

ajay

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-22 : 06:02:38

quote:

Thanks alot for responsding so fast.
However how to display totals also.

ajay

Oops I missed that bit. Either add all the individual sums together..

sum (case when type = 'article' and class = 'original' then 100
when type = 'article' and class = 'recommended' then 50
else 0 end) + sum (.....

Or you can extend the case statement inside the sum to cover all the possibilities..

sum (case when type = 'article' and class = 'original' then 100
when type = 'article' and class = 'recommended' then 50
when type = 'book' and class = 'original' then 30....

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-22 : 07:38:19

The only thing I would do is create a table called PointTable, primary key of Types and Classes, that stores how many points that combo should give you. I never embedd numbers like the above in the logic of a query. You wouldn't want to have to edit your query if you ever add more types or classes or want to change the points, and also it makes your query easier.

This also makes your point total MUCH easier.

That is,

SELECT
userid,
sum (case when t.type = 'article' THEN Points ELSE 0 END) as article,
sum (case when t.type = 'book' THEN Points ELSE 0 END) as book,
sum (case when t.type = 'tool' THEN Points ELSE 0 END) as tool,
sum (case when t.type = 'tip' THEN Points ELSE 0 END) as tip,
sum (Points) as PointTotal,
case when ifvisible = 1 then email else null end as hyperlink
FROM thingstable t
INNER JOIN users u ON
u.userid = t.userid
INNER JOIN PointTable ON
PointTable.Type = T.Type AND
PointTable.Class = T.Class
GROUP BY
userid, hyperlink

Shorter, easier to read, easier to maintain.

- Jeff

Edited by - jsmith8858 on 11/22/2002 07:40:16

Edited by - jsmith8858 on 11/22/2002 08:36:08

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-22 : 07:48:11

I will have to try to remember that.

-------
Moo.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources