Sharon,

I think you've been around long enough to know that you get better help by posting DDL and consumable sample data. But I'm feeling generous on this Monday morning so I'll set it up for you.

DECLARE @Current_Month DATETIME = '2014-01-14';

WITH SampleData (Configuration_Item, Request_Date) AS

(

SELECT 'INTEL', CAST('2013-06-05' AS DATETIME)

UNION ALL SELECT 'INTEL', '2013-10-01'

UNION ALL SELECT 'INTEL', '2013-10-12'

UNION ALL SELECT 'INTEL', '2013-11-05'

UNION ALL SELECT 'INTEL', '2013-12-04'

UNION ALL SELECT 'INTEL', '2013-12-11'

UNION ALL SELECT 'INTEL', '2014-01-05'

UNION ALL SELECT 'AMD', '2013-10-01'

UNION ALL SELECT 'AMD', '2013-10-12'

UNION ALL SELECT 'AMD', '2013-11-05'

UNION ALL SELECT 'AMD', '2013-12-04'

UNION ALL SELECT 'AMD', '2013-12-05'

UNION ALL SELECT 'AMD', '2013-12-15'

UNION ALL SELECT 'AMD', '2013-12-18'

UNION ALL SELECT 'AMD', '2014-01-05'

),

ConvertDaystoMonths AS

(

SELECT *

FROM SampleData a

CROSS APPLY

(

SELECT rd=DATEADD(month, DATEDIFF(month, 0, Request_Date), 0)

,cd=DATEADD(month, DATEDIFF(month, 0, @Current_Month), 0)

) b

)

SELECT Configuration_Item

,CurrentRequests=(

SELECT COUNT(*)

FROM ConvertDaystoMonths b

WHERE a.Configuration_Item = b.Configuration_Item AND

rd = cd

)

,PriorRequests=(

SELECT COUNT(*)

FROM ConvertDaystoMonths b

WHERE a.Configuration_Item = b.Configuration_Item AND

rd BETWEEN DATEADD(month, -3, cd) AND DATEADD(month, -1, cd)

)

FROM ConvertDaystoMonths a

GROUP BY Configuration_Item;

The basic idea is to group by your configuration items and perform 2 correlated sub-queries on your needed date ranges. Note how I have converted the request dates to the first of each month.