Select Totals

  • HI

    I have the following two queries, BUT would like to conbine then into one:

    SELECT DATENAME(year, sys_requestdate) AS Year, DATEPART(mm, sys_requestdate) AS Month_No, DATENAME(month, sys_requestdate) AS Month_Name,

    COUNT(sys_request_id) AS Opened

    FROM request

    GROUP BY DATENAME(month, sys_requestdate), DATENAME(year, sys_requestdate), DATEPART(mm, sys_requestdate)

    HAVING (DATENAME(year, sys_requestdate) = @year)

    ORDER BY Year, Month_No, Month_Name

    SELECT DATENAME(year, sys_requestclosedate) AS Year, DATEPART(mm, sys_requestclosedate) AS Month_No, DATENAME(month, sys_requestclosedate)

    AS Month_Name, COUNT(sys_requestclosedate) AS Closed

    FROM request

    GROUP BY DATENAME(month, sys_requestclosedate), DATENAME(year, sys_requestclosedate), DATEPART(mm, sys_requestclosedate)

    HAVING (DATENAME(year, sys_requestclosedate) = @year) AND (NOT (COUNT(sys_requestclosedate) IS NULL))

    ORDER BY Year, Month_No, Month_Name

    The result that in want is as follows

    Year, Month. Opened, Closed

    2009, January, 200,215

    2009, February, 250,157

    2009, March, 180,185

    Can anyone help?

    Thanks

    Greg

  • Greg,

    See how this works:

    SELECT DATENAME(year, sys_requestdate) AS Year, DATEPART(mm, sys_requestdate) AS Month_No, DATENAME(month, sys_requestdate) AS Month_Name,

    COUNT(sys_request_id) AS Opened, COUNT(sys_requestclosedate) AS Closed

    FROM request

    GROUP BY DATENAME(month, sys_requestdate), DATENAME(year, sys_requestdate), DATEPART(mm, sys_requestdate)

    HAVING (DATENAME(year, sys_requestdate) = @year)

    ORDER BY Year, Month_No, Month_Name

    To make it easier for forum users to help you in the future, you may want to include CREATE TABLE syntax with INSERT statements to provide sample data to test with 🙂

    _________________________________
    seth delconte
    http://sqlkeys.com

  • Thanks Seth, This works , BUT the problem is that the closed totals are a little out and it because the opened date could be in a previous month but closed in the month paramater. So I would need to have the query pull out all where the requestdate is for the month parameter and the requestclosedate where requestclosedate is = to the month parameter even if the requestdate is in a previouse month. Hope you can understand what I mean

    Thanks

    Greg

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply