July 22, 2008 at 9:12 am
Hi,
I am trying to write this count below to count team reports for a team. I need to restrict the results to a single selected season which basically means
the ScoutingReport.ReportDate is between Season.StartDate and Season.EndDate.
When i include the code in bold as a left join it counts every season but if its a normal join it excludes the teams who have no reports but i need them to be counted as 0's.
Any help or advice would be appreciated
Thanks in advance
Tim
Select
euc.CountryID,
c.Name as 'Club',
sr.ReportDate, euc.SeasonID,
Count(c.ID)/14 as 'Count',
(
Select Count(*)
From ScoutingCoverageEUCountries ch
Where ch.CountryID = euc.CountryID
) as 'MaxInLeague'
From Club c
Join ScoutingCoverageEUCountries euc on c.ID = euc.ClubID
Join Squad s on s.ClubID = c.ID And s.SquadName like '%1st Team%'
Left Join ScoutingTeam st on st.SquadID = s.ID
Left Join ScoutingReport sr on sr.ID Between st.MinScoutReportID And st.MaxScoutReportID
Left Join Seasons sea on sea.ID = 148 And euc.SeasonID = sea.ID And ReportDate Between sea.StartDate And sea.EndDate
Group By euc.CountryID, c.Name, c.ID, sr.ReportDate, euc.SeasonID
July 22, 2008 at 9:31 am
2 changes:
Count(c.ID)/14 as 'Count' to IsNull(Count(c.ID), 0)/14 will give you 0's
Move sea.ID = 148 to the where clause as euc.SeasonID = 148
Here is the code with the changes:
[font="Courier New"]SELECT
euc.CountryID,
c.Name AS Club,
sr.ReportDate,
euc.SeasonID,
ISNULL(COUNT(c.ID), 0)/14 AS COUNT,
(
SELECT COUNT(*)
FROM ScoutingCoverageEUCountries ch
WHERE ch.CountryID = euc.CountryID
) AS MaxInLeague
FROM
Club c JOIN
ScoutingCoverageEUCountries euc ON
c.ID = euc.ClubID JOIN
Squad s ON
s.ClubID = c.ID AND
s.SquadName LIKE '%1st Team%' LEFT JOIN
ScoutingTeam st ON
st.SquadID = s.ID LEFT JOIN
ScoutingReport sr ON
sr.ID BETWEEN st.MinScoutReportID AND st.MaxScoutReportID LEFT JOIN
Seasons sea ON
euc.SeasonID = sea.ID AND
sr.ReportDate BETWEEN sea.StartDate AND sea.EndDate
WHERE
euc.SeasonID = 148
GROUP BY
euc.CountryID,
c.Name,
c.ID,
sr.ReportDate,
euc.SeasonID[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 22, 2008 at 9:56 am
Thanks a million man, that had been driving me crazy for while now
Cheers
Tim
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply