SQL Server Performance Forum – Threads Archive

Max Date Prob

Hi i am havin a bit of truble tryin to get the max reportdate of an assessment the sql i have so far has returns all games just sorted by date im just wondering if anyone know where i shud be including the max to get a single max date and match name returned any help is greatly appreciated select dbo.pr_bll_GetOpponentTeamName(m.Id) + ‘ – ‘ + Substring(Cast(max(m.date)as nvarchar), 0, 12)
From MatchAssessment ma
Join Teams t on t.TeamId = ma.TeamID
Join Match m on m.Id = t.MatchID
Where ma.CoachID = 11620
And m.Date between ’07/01/05′ And ’06/30/06′
And m.CompetitionId = 0
Group by m.ID, m.Date
Order by m.Date desc

---

Drop the m.Date column from the GROUP BY clause. DO I spot a UDF just to do a lookup? Bad choice! Just add the lookup table with a join.

---

select dbo.pr_bll_GetOpponentTeamName(m.Id) + ‘ – ‘ + Substring(Cast(max(m.date)as nvarchar), 0, 12)
From MatchAssessment ma
Join Teams t on t.TeamId = ma.TeamID
Join Match m on m.Id = t.MatchID
Where ma.CoachID = 11620
And m.Date between ’07/01/05′ And ’06/30/06′
And m.CompetitionId = 0
Group by m.ID i have this now but im still gettin multiple rows not really sure what u mean by this im fairly new to developing
"DO I spot a UDF just to do a lookup? Bad choice! Just add the lookup table with a join."

---

You probably need to do the GROUP BY query as a derived table (= embedded query) and then do the formatting in the main query: SELECT T1.OpposingTeam + CAST(T1.MaxDate AS VARCHAR(20))
FROM
(SELECT Opponent.TeamName AS OpposingTeam, max(m.date) AS MaxDate
From MatchAssessment ma
Join Teams t on t.TeamId = ma.TeamID
JOIN Teams Opponent ON t.TeamID = Opponent.TeamID
Join Match m on m.Id = t.MatchID
Where ma.CoachID = 11620
And m.Date between ’07/01/05′ And ’06/30/06′
And m.CompetitionId = 0
Group By Opponent.TeamName) T1

---

thanks alot u gave me the idea i really appreciate it
this is how i did it select dbo.pr_bll_GetOpponentTeamName(m.id ) + ‘ – ‘ + Substring(Cast(max(m.date)as nvarchar), 0, 12)
From MatchAssessment ma
Join Teams t on t.TeamId = ma.TeamID
Join Match m on m.Id = t.MatchID
Where ma.CoachID = 11885
And m.Date between ’07/01/05′ And ’06/30/06′
And m.CompetitionId = 0
And m.Date = (select max(m.Date) From MatchAssessment ma
Join Teams t on t.TeamId = ma.TeamID
Join Match m on m.Id = t.MatchID
Where ma.CoachID = 11885
And m.Date between ’07/01/05′ And ’06/30/06′
And m.CompetitionId = 0)
Group by m.ID

---

tadhg88, What Adriaan means about the UDF is that you have a user defined function doing a query based on m.id. This is unadvisable if you can avoid it. Your query basically has to run the function for every row returned making lots of function calls and lots of additional queries. Additionally, the query optimizer that helps SQL Server speed things up is unable to do as good of a job. In order to do the same thing as the dbo.pr_bll_GetOpponentTeamName function, try doing it through joins. It might substantially increase the speed of your query. See Adriaan’s last post for a good example of what the query might look like. John

---

]]>