SQL Server Group BY Question

I have the following table:

CREATE TABLE #weeks(
  ProposalLineWeekID INT, 
  StartWeek DATETIME, 
  EndWeek DATETIME, 
  Selected BIT, 
  WeekNumber INT
)

How do I get all the ProposalLineWeekID values that have duplicate WeekNumber entries? For instance

ProposalLineWeekID, WeekNumber 
------------------------开发者_如何学Go------
2                   22
3                   6
4                   22

I'd like to get ProposalLineWeekID 2 and 4 with the accompanying WeekNumber.

Try this:

SELECT ProposalLineWeekID, WeekNumber
FROM #Weeks
WHERE WeekNumber IN
( 
    SELECT WeekNumber
    FROM #weeks
    GROUP BY WeekNumber
    HAVING COUNT(*) > 1
)

The subquery finds all the duplicated week numbers and the outer query fetches the corresponding rows.

If I'm understanding correctly, I think this is what you want:

SELECT DISTINCT w1.ProposalLineWeekID, w1.WeekNumber
FROM weeks w1
INNER JOIN weeks w2 ON w2.WeekNumber = w1.WeekNumber 
  AND w2.ProposalLineWeekID <> w1.ProposalLineWeekID