in grouping getting duplicate records

I want to filter records on the basis of month. Requirement is in month how much projects are in "completed, pending, started etc" status. This is my query but i am getting duplicate records.

SELECT distinct
        convert(varchar(7), w.ExpectedStartDate, 126) AS StatusOfMonth,
        COUNT(w.StatusTypeId) AS StatusCount,
        w.StatusTypeId,
        st.StatusTypeName

        FROM Table1 w
        LEFT OUTER JOIN StatusType st ON st.StatusTypeId = w.StatusTypeId
        WHERE   CONVERT(VARCHAR(20), w.ExpectedStartDate, 103) BETWEEN '10/01/2011' AND '14/04/2011'
GROUP BY ExpectedStartDate, w.StatusTypeId, st.StatusTypeName

Please see image to clarify what i want. Please let me know开发者_运维百科 how can i get correct results.

Looks like your grouping by the date, not by the month or by the status of month Group by

DATEPART(M, ExpectedStartDate)

or

convert(varchar(7), w.ExpectedStartDate, 126)

instead of just ExpectedStartDate

EDIT

In response the comment: Try getting rid of

convert(varchar(7), w.ExpectedStartDate, 126) AS StatusOfMonth

and just try it like this:

SELECT
    convert(varchar, datepart(yyyy, w.ExpectedStartDate)) + '-' + CONVERT(varchar(3), DATENAME(month, w.ExpectedStartDate)),
    w.StatusTypeId,
    st.StatusTypeName,
    COUNT(w.StatusTypeId) AS StatusCount
FROM 
    Table1 w LEFT OUTER JOIN 
    StatusType st ON st.StatusTypeId = w.StatusTypeId
WHERE   
    w.ExpectedStartDate BETWEEN '1/10/2011' AND '04/14/2011'
GROUP BY 
    datepart(M, w.ExpectedStartDate),
    datepart(yyyy, w.ExpectedStartDate),
    w.StatusTypeId,
    st.StatusTypeName