Calculating success rate in query

[HealthyLiving]

Hi all!!

I need to pull a table from this simple db:

User:

[p_user_id] [int] IDENTITY(1,1) NOT NULL,
[fname] [varchar](50) NOT NULL,
[sname] [varchar](50) NOT NULL,
[varchar](50) NOT NULL,
[accesslevel] [int] NOT NULL,
[password] [varchar](50) NOT NULL,
[lastlogin] [datetime] NULL,
[logincount] [int] NULL,

Event:

[p_event_id] [int] IDENTITY(1,1) NOT NULL,
[eventdate] [datetime] NOT NULL,
[eventtype] [varchar](50) NOT NULL,
[firsthalfcolour] [int] NULL,
[secondhalfcolour] [int] NULL,
[firsthalfscore] [int] NULL,
[secondhalfscore] [int] NULL,

and Link:

[p_link_id] [int] IDENTITY(1,1) NOT NULL,
[f_event_id] [int] NOT NULL,
[f_user_id] [int] NOT NULL,
[f_colour_id] [int] NOT NULL,

And I need to get a list of all the users with their win percentage. firsthalfcolour (winning colour) = f_colour_id (your team colour).

I have this to calculate an individuals percentage but I dont know how to get a 'group' overview?:

CREATE procedure [dbo].[getwinpercentage_byuserid]
(
@userid int
)
as
declare @First integer
declare @Second integer
declare @Third integer


/* First half wins*/
select @First = count(*) from [event]
left join [link] on f_event_id = p_event_id
left join colour on firsthalfcolour = p_colour_id
where f_user_id = @userid and firsthalfcolour = f_colour_id

/* second half wins*/
select @Second = count(*) from [event]
left join [link] on f_event_id = p_event_id
left join colour on secondhalfcolour = p_colour_id
where f_user_id = @userid and secondhalfcolour = f_colour_id

/* Match attendance*/
select @Third = count(*) from [event]
left join [link] on f_event_id = p_event_id
left join colour on firsthalfcolour = p_colour_id
where f_user_id = @userid


select (CONVERT(varchar, ((@First + @Second) * 100) / (@Third * 2)) + '%') as 'Success rate'

If you can help it would be greatly appreciated!

 

[kjv1611]

So, is this the only piece you really need help with of your whole situation?

I have this to calculate an individuals percentage but I dont know how to get a 'group' overview?:

If so, do you mean you need a summary of the percentages, and you don't know how to create your summary, or that you don't know how to get the percentages?

If the summary, are you wanting to get something like the following?

[TT]
Percentage Count of Users
0% to 20% 50
>20% to 50% 20
>50% to 75% 10
>75% 5
[/TT]

If so, there's a forum thread where I got some help along those lines a while back which you might find helpful.

It is this one:

http://www.tek-tips.com/viewthread.cfm?qid=1464722&page=6

--

"If to err is human, then I must be some kind of human!" -Me