选择 
（选择 COUNT（*）来自 HX_Issue 其中 ProjectId = 1 和 ComponentId = 13 和 CreatedOn   '  2012/12/24' 和 '  2013/01/30' 和 StatusId！= 23） as  OpenIssues 
，
（选择 COUNT（*）来自 HX_Issue 其中 ProjectId = 1 和 ComponentId = 13 和 CreatedOn   '  2012/12/24 ' 和 '  2013/01/30 ' 和 StatusId = 23） as 关闭

更新：根据新要求



选择
distinct DATEPART（month，exq.CreatedOn）
（选择 COUNT（*）来自 HX_Issue inq 其中 inq.ProjectId = 1 和 inq.ComponentId = 13 和 DATEPART（月，inq .CreatedOn）= DATEPART（月，exq.CreatedOn）和 inq.CreatedOn ' 2012/12/24' 和 ' 2013/01/30' 和 StatusId！= 23） OpenIssues，
（选择 COUNT （*）来自 HX_Issue inq 其中 inq.ProjectId = 1 和 inq.ComponentId = 13 和 DATEPART（月，inq.CreatedOn）= DATEPART（月，exq.CreatedOn）inq.CreatedOn 介于 ' 2012/12/24' 和 ' 2013/01/30' 和 StatusId = 23） as ClosedIssues，
来自 HX_Issue exq
其中 ProjectId = 1 和 ComponentId = 13 和 exq.CreatedOn ' 2012/12/24' 和n> ' 2013/01/30'

甚至更好：

 选择 
 DATEPART（月，CreatedOn）为月，
 COUNT（案例 何时 StatusId！= 23 然后  1   else   null 

end ） as OpenIssues，
COUNT（ case 当 StatusId = 23 然后 1 else null end ） as ClosedIssues
来自 HX_Issue 其中 ProjectId = 1 和 ComponentId = 13
和 CreatedOn ' 2012/12/24 ' 和 ' 2013/01/30 '
group 按 DATEPART（month，CreatedOn）

Update2：基于更新的要求

  SET   ANSI_NULLS   ON  
 GO  
 SET   QUOTED_IDENTIFIER   ON  
 GO  
  -    =========================================== ====  
  -   作者：ZoltánZörgő 
  -   创建日期：2013.01.31  
 -    ================================== ===========  
 ALTER   PROCEDURE  [ dbo]。[IssueReport] 
 @ ProjectId   int ，
 @ ComponentId   int ，
 @ FromDate  日期，
 @ ToDate  日期 
 AS  
 BEGIN  
 SET   NOCOUNT   ON ; 
  DateCte  as  
（
 select  cast（ @ FromDate   as   datetime ）DateValue 
 union   all  
选择 DateValue +  1  
来自 DateCte 
其中 DateValue +  1 < =  @ ToDate  
）
选择 
 DATEPART（年，日期值）为年，
 DATEPART（月，DateValue）为月，
（选择 COUNT（*）来自 HX_Issue 其中 ProjectId = @ ProjectId 和 ComponentId = @ ComponentId 和 DATEPART（月，CreatedOn）= DATEPART（月，DateValue）和 DATEPART（年，CreatedOn）= DATEPART（年，DateValue）和 StatusId！= 23）  OpenIssues，
（选择 COUNT（*）来自 HX_Issue 其中 ProjectId = @ProjectId 和 ComponentId = @ ComponentId 和 DATEPART（month，CreatedOn）= DATEPART（month，DateValue）和 DATEPART（year，CreatedOn）= DATEPART（年，DateValue）和 StatusId = 23） as  ClosedIssues 
来自 DateCte 
 group  按 DATEPART（年，日期值），DATEPART（月， DateValue）
 OPTION （MAXRECURSION  0 ）
结束 

以下是使用示例：

  EXEC  [dbo]。[IssueReport] 
 @ ProjectId  =  1 ，
 @ ComponentId  =  13 ， 
 @ FromDate  = '  2012.01.01' ，
 @ ToDate  = '  2013.02.15' 

 GO  

Hi,

I am very poor in writing sql queryies please help me to get two columns as a result set like
OpenIssues ClosedIssues

50 1

By the following query but i am getting in rows like

OpenIssues
50
1

The query as follows :

select COUNT(*) as OpenIssues from HX_Issue where ProjectId=1 and ComponentId=13 and  CreatedOn between '2012/12/24' and '2013/01/30' and StatusId !=23
union all
select COUNT(*) as closed from HX_Issue where ProjectId=1 and ComponentId=13 and  CreatedOn between '2012/12/24' and '2013/01/30' and StatusId =23

select
(select COUNT(*) from HX_Issue where ProjectId=1 and ComponentId=13 and  CreatedOn between '2012/12/24' and '2013/01/30' and StatusId !=23) as OpenIssues
,
(select COUNT(*) from HX_Issue where ProjectId=1 and ComponentId=13 and  CreatedOn between '2012/12/24' and '2013/01/30' and StatusId =23) as closed

Update: based on "new requirement"

select 
distinct DATEPART(month, exq.CreatedOn)
(select COUNT(*) from HX_Issue inq where inq.ProjectId=1 and inq.ComponentId=13 and  DATEPART(month, inq.CreatedOn)=DATEPART(month, exq.CreatedOn) and inq.CreatedOn between '2012/12/24' and '2013/01/30' and StatusId !=23) as OpenIssues,
(select COUNT(*) from HX_Issue inq where inq.ProjectId=1 and inq.ComponentId=13 and  DATEPART(month, inq.CreatedOn)=DATEPART(month, exq.CreatedOn) inq.CreatedOn between '2012/12/24' and '2013/01/30' and StatusId =23) as ClosedIssues,
from HX_Issue exq
where ProjectId=1 and ComponentId=13 and exq.CreatedOn between '2012/12/24' and '2013/01/30'

Or even better:

select 
DATEPART(month, CreatedOn) as month,
COUNT(case when StatusId!=23 then 1 else null end) as OpenIssues,
COUNT(case when StatusId=23 then 1 else null end) as ClosedIssues
from HX_Issue where ProjectId=1 and ComponentId=13 
and CreatedOn between '2012/12/24' and '2013/01/30'
group by DATEPART(month, CreatedOn)

Update2: based on even newer requirements

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Zoltán Zörgő
-- Create date: 2013.01.31
-- =============================================
ALTER PROCEDURE [dbo].[IssueReport] 
	@ProjectId int,
	@ComponentId int,
	@FromDate Date,
	@ToDate Date
AS
BEGIN
	SET NOCOUNT ON;
	with DateCte as
     (
         select cast(@FromDate as datetime) DateValue
         union all
         select DateValue + 1
         from    DateCte   
         where   DateValue + 1 <= @ToDate
     )
   select 
		DATEPART(year, DateValue) as year, 
		DATEPART(month, DateValue) as month,
		(select COUNT(*) from HX_Issue where ProjectId=@ProjectId and ComponentId=@ComponentId and DATEPART(month, CreatedOn)=DATEPART(month, DateValue) and DATEPART(year, CreatedOn)=DATEPART(year, DateValue) and StatusId !=23) as OpenIssues,
	    (select COUNT(*) from HX_Issue where ProjectId=@ProjectId and ComponentId=@ComponentId and DATEPART(month, CreatedOn)=DATEPART(month, DateValue) and DATEPART(year, CreatedOn)=DATEPART(year, DateValue) and StatusId =23) as ClosedIssues
   from  DateCte
   group by DATEPART(year, DateValue), DATEPART(month, DateValue)
   OPTION (MAXRECURSION 0)
END

And here is the usage sample:

EXEC    [dbo].[IssueReport]
        @ProjectId = 1,
        @ComponentId = 13,
        @FromDate = '2012.01.01',
        @ToDate = '2013.02.15'

GO