I have a table that has two dates in it, a date opened and a date
closed. I would like to create one query to give me the number of
records that have been opened each month plus, and this is the hard
part the number of those records that have been closed each month. I
can get the result with two seperate queries but have been unable to
get it combined into one query with three values for each month, i.e.,
the month, the number opened and the number of those that were opened
in the month that have been subsequently closed.

Here's my two queries. If anyone can help I'd appreciate.

SELECT COUNT(*) AS [Number Closed], LEFT(DATENAME(m, DateOpened),
3) + '
' + CAST(YEAR(DateOpened) AS Char(5)) AS [Month Opened]
FROM table
WHERE (DateClosed IS NOT NULL)
GROUP BY CONVERT(CHAR(7), DateOpened, 120), LEFT(DATENAME(m,
DateOpened), 3)
+ ' ' + CAST(YEAR(DateOpened) AS Char(5))
ORDER BY CONVERT(CHAR(7), DateOpened, 120)

SELECT COUNT(*) AS [Number Opened], LEFT(DATENAME(m, DateOpened),
3) + '
' + CAST(YEAR(DateOpened) AS Char(5)) AS [Month Opened]
FROM table
GROUP BY CONVERT(CHAR(7), DateOpened, 120), LEFT(DATENAME(m,
DateOpened), 3)
+ ' ' + CAST(YEAR(DateOpened) AS Char(5))
ORDER BY CONVERT(CHAR(7), DateOpened, 120)

TIA

Bill

Try:

SELECT MIN(dateopened),
COUNT(*),
COUNT(dateclosed)
FROM YourTable
GROUP BY YEAR(dateopened), MONTH(dateopened)

--
David Portas
SQL Server MVP
--

David;

Thank you very much that works just fine. I appreciate the help.

Cheers;

Bill