>Let's a say that I have a simple table that has columns WIDGETNAME, SALEDATE. The task is to create a table with columns WIDGETNAME, SALESPERMONTH, RUNNINGTOTAL. A requirement is that months with zero sales are to be included in the final tally. So for a given widget, the final table might look like this:

>Note:  SPM=sales per month
>Item         Month  SPM RunningTot
>THISWIDGET  2005/01 002 002
>THISWIDGET  2005/02 001 003
>THISWIDGET  2005/03 010 013
>THISWIDGET  2005/04 000 013
>THISWIDGET  2005/05 002 015

>
>This is not particularly onerous to do with some VFP code (and an application of INDEX UNIQUE that I like to use) layered on top of some SQL. But I was wondering if anybody has an SQL-only solution for this? It seems this would be a commonly requested type of summary. Thanks very much.

This query should give you some ideas. It generates a result showing every combination of product id and employee id for a given year (this is Northwind data).

This big thing you need to do is create a cursor that contains a list of months, so you can join that to the data you have.

SELECT CrossProd.ProductID, ;
       CrossProd.EmployeeID, ;
       m.nYear as Year, ;
       NVL(UnitsSold, 0), NVL(TotalSales, $0);
	FROM (SELECT Employees.EmployeeID, Products.ProductID ;
		FROM Employees, Products) AS CrossProd ;
	  LEFT JOIN ( ;
		SELECT ProductID, EmployeeID, ;
		       SUM(Quantity) AS UnitsSold, ;
		       SUM(Quantity * UnitPrice) AS TotalSales ;
			FROM Orders ;
			  JOIN OrderDetails ;
			    ON Orders.OrderID = OrderDetails.OrderID ;
			WHERE YEAR(OrderDate) = m.nYear ;
			GROUP BY ProductID, EmployeeID ) AS AnnualSales ;
		ON CrossProd.EmployeeID = AnnualSales.EmployeeID  ;
		AND CrossProd.ProductID = AnnualSales.ProductID

Tamar