Hi all, I am trying to write a query to create the followingtable... excluding the duplicate rowshttp://www.screenshots.cc/show.php/15842_tablepic.JPG.html sothat each player has a row for each day of the week in the db. the codebelow seemed to work ok until i included a second row for a playerduring the same week and now i have duplicate rows in the results. Select p.ID, IsNull(firstName+ ' ','') + IsNull(LastName,'') as Name, Case when DATENAME(WEEKDAY, available.Date) = 'Monday' then om.Name when DATENAME(WEEKDAY, unavailable.Date) = 'Monday' then om1.Name end as Mon, Case when DATENAME(WEEKDAY, available.Date) = 'Tuesday' then om.Name when DATENAME(WEEKDAY, unavailable.Date) = 'Tuesday' then om1.Name end as Tue, Casewhen DATENAME(WEEKDAY, available.Date) = 'Wednesday' then om.Name whenDATENAME(WEEKDAY, unavailable.Date) = 'Wednesday' then om1.Name end asWed, Case when DATENAME(WEEKDAY, available.Date) = 'Thursday' thenom.Name when DATENAME(WEEKDAY, unavailable.Date) = 'Thursday' thenom1.Name end as Thu, Case when DATENAME(WEEKDAY, available.Date) ='Friday' then om.Name when DATENAME(WEEKDAY, unavailable.Date) ='Friday' then om1.Name end as Fri, Case when DATENAME(WEEKDAY,available.Date) = 'Saturday' then om.Name when DATENAME(WEEKDAY,unavailable.Date) = 'Saturday' then om1.Name end as Sat, Case whenDATENAME(WEEKDAY, available.Date) = 'Sunday' then om.Name whenDATENAME(WEEKDAY, unavailable.Date) = 'Sunday' then om1.Name end as Sun From Person p Join SquadPlayerMapping spm on spm.PlayerId = p.ID Join SquadMainPlayers smp on smp.PlayerID = p.ID And smp.SeasonID = 148 Join Seasons sea on sea.ID = smp.SeasonID Join RedZoneWeeklyMapping rzwm on rzwm.ID = 139 LeftJoin DailyPlayerAvailability available on available.PlayerID = p.ID Andavailable.Date Between rzwm.StartDate And rzwm.EndDate Left Join OptionsMapping om on om.ID = available.ReasonID LeftJoin DailyPlayerunAvailability unavailable on unavailable.PlayerID =p.ID And unavailable.Date Between rzwm.StartDate And rzwm.EndDate Left Join OptionsMapping om1 on om1.ID = unavailable.ReasonID Where spm.SquadID = 5 And (((spm.DateLeft IS NULL) And (spm.DateEntered = sea.StartDate) And (spm.DateLeft <= sea.EndDate))) And spm.SquadID = smp.SquadID --Group By p.ID, p.firstName, p.LastName, om.Name, om1.Name, available.Date, unavailable.Date Order by p.Lastname Im just wondering if anyone has any ideas how to remove these duplicate rows from the results Thanks in advance Tim
There are two hyphens (--) at the start of one of the lines. Do you know the effect of (a) these two hyphens, and (b) the line after the two hyphens?
Hi, Thanks for your reply and yes i do know the effect this line should have but in this instance it does not have any effect but i dont know why... Tim
Use the select query, but without the aggregate expressions in the SELECT clause. Does this give you the right number of rows? Then add one aggregate expression at a time, and check the number of rows again. Repeat, replacing the first aggregate expression with a different one, etc. Repeat with combinations of aggregate expressions. This should help you understand what causes the 'duplicates'.
