April 26, 2006 at 5:02 am
Hi i am tryin to count the different types of injuries that players have had and display them in a stacked column chart i can manage
to count the total amount of injuries for each player but im not sure how to go about countin the different types in the same query
select IsNull(firstName+ ' ','') + IsNull(MiddleName+ ' ','') + IsNull(LastName,'') as Name, count(i.PersonId) as 'Games Missed'
from injury i
Left Join Person p on i.PersonID = p.ID
Left Join teams t on t.PlayerId = p.ID
Left Join Match m on m.ID = t.MatchID
Left Join SquadPlayerMapping spm on spm.PlayerID = i.PersonID
Where m.Date between i.Date and i.DateRecovered
And spm.SquadId = 5
And ((i.MechanismOfInjury in (-1)) or (-1 in (-1)))
group by i.PersonId, firstName, MiddleName, LastName
the i.MechanismOfInjury in -1 represents all the injuries bein counted
but the different options that need to be counted are (13), (14), (15, 16)
i wud really appreciate any help that can be offered thanks in advance
May 1, 2006 at 8:00 am
This was removed by the editor as SPAM
May 1, 2006 at 11:11 am
select
IsNull(firstName+ ' ','') + IsNull(MiddleName+ ' ','') + IsNull(LastName,'') as Name,
CASE E.Id
WHEN -1 THEN "Games Missed"
WHEN 13 THEN "Desc for 13"
WHEN 14 THEN "Desc for 14"
WHEN 15 THEN "Desc for 15"
WHEN 16 THEN "Desc for 16"
END,
count(i.PersonId)
from
injury i
Left Join Person p
on i.PersonID = p.ID
Left Join teams t
on t.PlayerId = p.ID
Left Join Match m
on m.ID = t.MatchID
Left Join SquadPlayerMapping spm
on spm.PlayerID = i.PersonID
JOIN
(
SELECT -1 Id
UNION ALL
SELECT 13
UNION ALL
SELECT 14
UNION ALL
SELECT 15
UNION ALL
SELECT 16 ) E
Where
m.Date between i.Date and i.DateRecovered
and spm.SquadId = 5
and (i.MechanismOfInjury = E.Id)
group by
i.PersonId,
e.Id,
firstName,
MiddleName,
LastName
May 2, 2006 at 4:33 am
sorry about this but i cant seem to find the problem with this statement
...............Incorrect syntax near the keyword 'Where'.
May 2, 2006 at 9:59 am
select
IsNull(firstName+ ' ','') + IsNull(MiddleName+ ' ','') + IsNull(LastName,'') as Name,
CASE E.Id
WHEN -1 THEN "Games Missed"
WHEN 13 THEN "Desc for 13"
WHEN 14 THEN "Desc for 14"
WHEN 15 THEN "Desc for 15"
WHEN 16 THEN "Desc for 16"
END,
count(i.PersonId)
from
injury i
Left Join Person p
on i.PersonID = p.ID
Left Join teams t
on t.PlayerId = p.ID
Left Join Match m
on m.ID = t.MatchID
Left Join SquadPlayerMapping spm
on spm.PlayerID = i.PersonID
JOIN
(
SELECT -1 Id
UNION ALL
SELECT 13
UNION ALL
SELECT 14
UNION ALL
SELECT 15
UNION ALL
SELECT 16 ) E
ON i.MechanismOfInjury = E.Id
WHERE m.Date between i.Date and i.DateRecovered
and spm.SquadId = 5
group by
i.PersonId,
e.Id,
firstName,
MiddleName,
LastName
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply