SQL Query to produce Monthly Turnover by Customer, inc Months with zero Invoices (for Graphing)

This is one of the samples that I've tried - it produces monthly totals, but only for months that have an invoice!  I need it to show all months between the date ranges, and a 0 if necessary for graphing purposes.

DECLARE @StartDate SMALLDATETIME, @EndDate SMALLDATETIME;

SELECT @StartDate = '20140501', @EndDate = '20150529';

;WITH d(d) AS
(
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0))
  FROM ( SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) + 1)
    n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
    FROM sys.all_objects ORDER BY [object_id] ) AS n
)
SELECT
  [Month]    = DATENAME(MONTH, d.d),
  [Year]     = YEAR(d.d),
  InvTotal = SUM(o.InvoiceAmt)
FROM d LEFT JOIN InvcHead AS o
  ON o.InvoiceDate >= d.d
  AND o.InvoiceDate < DATEADD(MONTH, 1, d.d)
  WHERE CustNum = 233
GROUP BY d.d
ORDER BY d.d;

If you can't get a script to work I can probably figure out a way to have it done in a subreport.

mlmcc

vasto - how does that link to my other table?  the problem always seems to be when linking to the other table.  Before I apply the WHERE clause in the code I posted it works - I can a listing which includes some null entries, but has the month.

mlmcc - I am intending this to go into a subreport anyway, building a customer summary sheet including account balance, turnover, current orders, rebates etc.

WHY even with a LEFT OUTER JOIN does it only show records matching in both tables?  All other instances a LEFT OUTER would show me all of the dates, and only records from the right that match!

Are you filtering on the joined table?
If so then Crystal converts the join to INNER since a NULL in the joined table can't match the value.

mlmcc

Thanks very much for all the help - after all that comparing what I posted to what mlmcc provided was only 1 line different, but that line made all the difference!  Thanks for helping me to arrive at the solution.

One other point worth noting if anybody is reading this later and trying to the same kind of thing in Crystal - my query above is sorted in date order, but I charted this in Crystal it decided to ORDER the dataset in what it calls ascending order - which is an alpha sort.  Once I changed it back to "in original order" but graph looks as intended.

I also converted the @EndDate to GetDATE() to pull current date, and changed the @StartDate to be DATEADD(month, -11, @EndDate) to make the date range dynamic.  I then created a parameter for CustNum, and that gets set on the subreport from the main report.

Many Thanks
Mark