Challenge - 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

Site Related Forums

The Yak Corral

Challenge

Author

Topic

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-05 : 22:26:18

OK guys (and I mean that in the non-gender-specific and gender-affirming way),

I need a nice cute solution for the following. I want to find out the preferences of my team for their weekly reward. To this end I have offered them 5 options, Twisties, Lollie-gobble-bliss-bombs, chocolate, crisps or jelly-babies. Each person gets to vote once on their top 3 preferences for reward. Their first preference gets 3 points, the second 2 points, their last preference gets 1 point.

They can put down the same item for their first AND second AND third preference if its the only thing they like. The data is stored in two tables. Options, and Choices.

I'm after the query which returns the scores for each option.

For example,
if john votes for
1 for Chocolate
2 for Twisties
and 3 for Lollie-gobble-bliss-bombs
and brunhilda votes
1 for Twisties
2 for Jelly-babies
and 3 for Lollie-gobble-bliss-bombs

then the results are:
Option, Score
-------------
Twisties, 5
Chocolate, 3
Jelly-babies, 2
Lollie-gobble-bliss-bombs, 2

DDL and example data is below:

create table Options(id Integer, OptionName nvarchar(100))
insert into Options(id, OptionName) values(1 , 'Twisties')
insert into Options(id, OptionName) values(2 , 'Lollie-gobble-Bliss-Bombs')
insert into Options(id, OptionName) values(3 , 'Chocolate')
insert into Options(id, OptionName) values(4 , 'Crisps')
insert into Options(id, OptionName) values(5 , 'Jelly-babies')


create table Choices(id Integer, namecode nvarchar(100), option1 Integer, option2 Integer, option3 Integer)
insert into Choices(id, namecode, option1, option2, option3) values(1 , 'rob' , 1 , 2 , 3)
insert into Choices(id, namecode, option1, option2, option3) values(2 , 'jim' , 2 , 4 , 5)
insert into Choices(id, namecode, option1, option2, option3) values(3 , 'nancy' , 1 , 1 , 2)
insert into Choices(id, namecode, option1, option2, option3) values(4 , 'phil' , 2 , 4 , null)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-04-05 : 22:55:55

Does this qualify??


create table #Options(id Integer, OptionName nvarchar(100))
insert into #Options(id, OptionName) values(1 , 'Twisties')
insert into #Options(id, OptionName) values(2 , 'Lollie-gobble-Bliss-Bombs')
insert into #Options(id, OptionName) values(3 , 'Chocolate')
insert into #Options(id, OptionName) values(4 , 'Crisps')
insert into #Options(id, OptionName) values(5 , 'Jelly-babies')


create table #Choices(id Integer, namecode nvarchar(100), option1 Integer, option2 Integer, option3 Integer)
insert into #Choices(id, namecode, option1, option2, option3) values(1 , 'rob' , 1 , 2 , 3)
insert into #Choices(id, namecode, option1, option2, option3) values(2 , 'jim' , 2 , 4 , 5)
insert into #Choices(id, namecode, option1, option2, option3) values(3 , 'nancy' , 1 , 1 , 2)
insert into #Choices(id, namecode, option1, option2, option3) values(4 , 'phil' , 2 , 4 , null)


Select Id, OptionName, Score= sum(score)
From 
	(
	Select B.Id, B.OptionName, Score=3 From #choices A Inner Join #options B On A.option1 = B.id Union All
	Select B.Id, B.OptionName, Score=2 From #choices A Inner Join #options B On A.option2 = B.id Union All
	Select B.Id, B.OptionName, Score=1 From #choices A Inner Join #options B On A.option3 = B.id
	) Z
Group By Id, OptionName

Drop Table #choices
Drop Table #options

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-05 : 23:01:38

Perfect

Not only does it qualify - It wins!! I'll post you a lollie-gobble-bliss-bomb as your prize...

Sorry - for the previous post - the copy paste from the page somehow left out the "All" from the Unions!

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-04-06 : 08:08:18

You Stole the doctors Jelly-babies !!!!!

Note: Just us across the pond, Doctor Who is the only context I have heard the term Jelly-babies used.

Jim
Users <> Logic

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-04-06 : 14:15:05

Actually... At first I left off the 'All's. I then realized my great error

and promptly fixed it. Thanks for the prize though

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-06 : 19:41:13

Well, that makes me feel better, coz I was sitting here scratching my head trying to work out how a select, cut and paste missed out two words from the middle of the text!!

Hey JimL

Does that mean you've heard of twisties and lollie-gobble-bliss-bombs? I thought they were Aussie icons!?

As for Doctor Who - if he's had his Jelly-babies stolen, he should drop by the Gawler Coles and pick up a packet...$1.47

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-04-07 : 07:49:31

Well when I was a Kid Twisties here were black licorice not cheese.
And after googling Lollie-gobble-bliss-bombs they are similar to something one of my aunts used to make.

Another instance of our differences making life more interesting.

I wonder if Gawler Coles was a pre-select destination on the Tardis?

Jim
Users <> Logic

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-07 : 19:51:06

hmmm...

I forgot to mention that all our aussie icons are made by foreign multinationals

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources