Sami
Sami

Reputation: 3976

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 see image to clarify what i want. Please let me know how can i get correct results.

Upvotes: 1

Views: 98

Answers (1)

Jeff Sheldon
Jeff Sheldon

Reputation: 2094

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

Upvotes: 4

Related Questions