SQL Server Performance

SQL Query

Discussion in 'General Developer Questions' started by tadhg88, May 14, 2008.

  1. tadhg88 New Member

    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
  2. Adriaan New Member

    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?
  3. tadhg88 New Member

    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
  4. Adriaan New Member

    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'.

Share This Page