Orders in last 24 months

SQL Apprentice Question
I would like to count the number of orders for each month for each one of my
customers over the last 24 months. The information will then be used into a
Crystal Report Bar Chart.

The problem I'm having is that Crystal will only display information that
exists. Some of my customers do not have orders in every months of the last 2
years.


I would like to create a view that will return 0 for each month that each
customer does not have any orders.


I have founs a few articles on creating a Calendar Table to help the process
but I cannot get the result I'm looking for.


Celko Answers
Fill this table with the last 24 months you want to look at:

CRERATE TABLE ReportPeriods
(rpt_period_name CHAR(10) NOT NULL PRIMARY KEY,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date));


Then this is your query:


SELECT R.rpt_period_name, SUM(sales_amt) AS sales_month_tot
FROM ReportPeriods AS R
LEFT OUTER JOIN
Sales AS S
ON S.sale_date BETWEEN start_date AND end_date
WHERE S.cust_id = @my_guy
GROUP BY R.rpt_period_name;