May 14, 2008 at 9:30 am
Hi all,
I am trying to write a query to create the following table... excluding the duplicate rows http://www.screenshots.cc/show.php/15842_tablepic.JPG.html
so that each player has a row for each day of the week in the db. the code below seemed to work ok until i included a second row for a player during 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,
Case when DATENAME(WEEKDAY, available.Date) = 'Wednesday' then om.Name when DATENAME(WEEKDAY, unavailable.Date) = 'Wednesday' then om1.Name end as Wed,
Case when DATENAME(WEEKDAY, available.Date) = 'Thursday' then om.Name when DATENAME(WEEKDAY, unavailable.Date) = 'Thursday' then om1.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 when DATENAME(WEEKDAY, available.Date) = 'Sunday' then om.Name when DATENAME(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
Left Join DailyPlayerAvailability available on available.PlayerID = p.ID And available.Date Between rzwm.StartDate And rzwm.EndDate
Left Join OptionsMapping om on om.ID = available.ReasonID
Left Join 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
May 14, 2008 at 10:00 am
There are two diferent approaches to remove duplicates as far as I know.
1. Retrieve distinct data into a new table, drop the old one, and rename the new table.
2. Create an identity column on the table, remove the duplicates, which have larger identity value.
May the above help.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
