Guess I should be a little more clear.

I am looking to count the # of requests for the current month and last 3 months(Static).

But don't know how COALesce will be useful to join the count part...

The output should look like:

Configuration Items CountRequestsJan CountRequestOld

Intel 20 10

AMD 3 5

With CTECurrent

As

(

SELECT

P.Configuration_Item,

P.TotalDays,

COUNT(P.Requests) AS CountRequestsJan

FROM Products P

WHERE

(P.Configuration_Item Like '%Intel%' OR Configuration_Item Like '%AMD%’)

And

P.TotalDays between '2014-01-01 00:00:00.0000000' and GETDATE()

Group By P.Configuration_Item,

P.TotalDays

)

, CTEPast

As

(

SELECT

P.Configuration_Item,

P.TotalDays,

COUNT(Tickets) AS CountRequestOld

FROM Products P

WHERE

(P.Configuration_Item Like '%Intel%' OR P.Configuration_Item Like '%AMD%’)

And

P.TotalDays between '2014-01-01 00:00:00.0000000' and '2013-10-01 00:00:00.0000000’

Group By P.Configuration_Item,

P.TotalDays

)

***** Left or Right Join messes up the count.