Quite few ways of doing this, in essence create a set of all customers/months and then do the count.

The server normally translates queries such as "select * from A left outer join B where B.xx = yy" to an inner join.

😎

/* For lack of other data */

USE AdventureWorks2012;

GO

;WITH ORDER_MONTH AS

(

/* All Order Months */

SELECT DISTINCT

(YEAR(SOH.OrderDate) * 100) + MONTH(SOH.OrderDate) AS ISO_MONTH

FROM Sales.SalesOrderHeader SOH

)

,CUST_MONTH AS

(

/* All Customer Month combinations */

SELECT

SACU.CustomerID

,OM.ISO_MONTH

FROM Sales.Customer SACU

CROSS APPLY ORDER_MONTH OM

)

,CUST_MONTH_COUNT AS

(

SELECT

ROW_NUMBER() OVER

(

PARTITION BY

CM.CustomerID

,CM.ISO_MONTH

ORDER BY

(SELECT NULL)

) AS CUMO_RID

,CM.CustomerID

,CM.ISO_MONTH

,ISNULL(COUNT(SOH.SalesOrderID) OVER

(

PARTITION BY

CM.CustomerID

,YEAR(SOH.OrderDate), MONTH(SOH.OrderDate)

),0) AS ORDER_COUNT

FROM CUST_MONTH CM

LEFT OUTER JOIN Sales.SalesOrderHeader SOH

ON CM.CustomerID = SOH.CustomerID

AND CM.ISO_MONTH = (YEAR(SOH.OrderDate) * 100) + MONTH(SOH.OrderDate)

)

SELECT

CMC.CustomerID

,CMC.ISO_MONTH

,CMC.ORDER_COUNT

FROM CUST_MONTH_COUNT CMC

WHERE CMC.CUMO_RID = 1

AND CMC.ISO_MONTH = 200803;