/*I'd be very surprised if you've ever used the old COMPUTE clause in SQL Server. It is a bit mad, and it is flagged for deprecation. It is an old Sybase legacy that was used when reports were printed directly from the output of SQL. You can still do it, but the world has really moved on. You can't use it for much else, because the output consists of a mass of separate results, and the only way you'd ever sensibly catch that lot is from the output of SQLCMD, and even then you'd have to parse it back into something that you could manipulate. Don't even think about it. It was great for directly printing out in a monospace font to a printer. In those days, you'd have just sent the output to the printer with a >PRN and then you'd be laughing.
Here is an example of its use, with AdventureWorks...*/
USE AdventureWorks;
GO
SELECT name, totalDue, OrderMonth FROM (
SELECT [name]=CONVERT(VARCHAR(12),RIGHT(HumanResources.Employee.LoginID, CHARINDEX('\',REVERSE(HumanResources.Employee.LoginID)+'\')-1)),
[orderMonth]=CONVERT(DATETIME,'1 '+RIGHT(CONVERT(CHAR(11),Sales.SalesOrderHeader.OrderDate,113),8)),
Sales.SalesOrderHeader.TotalDue
FROM HumanResources.Employee INNER JOIN
Sales.SalesPerson ON Sales.SalesPerson.SalesPersonID = HumanResources.Employee.EmployeeID INNER JOIN
Sales.SalesOrderHeader ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.SalesPersonID
)salesReport
ORDER BY OrderMonth, name
COMPUTE SUM(totalDue) BY OrderMonth,name
/* which gives...
name totalDue OrderMonth
------------ --------------------- -----------------------
david8 19005.2087 2001-07-01 00:00:00.000
david8 974.0229 2001-07-01 00:00:00.000
david8 10784.9873 2001-07-01 00:00:00.000
david8 56729.9942 2001-07-01 00:00:00.000
david8 4592.3356 2001-07-01 00:00:00.000
sum
---------------------
92086.5487
name totalDue OrderMonth
------------ --------------------- -----------------------
garrett1 10319.3341 2001-07-01 00:00:00.000
garrett1 1737.6562 2001-07-01 00:00:00.000
sum
---------------------
12056.9903
name totalDue OrderMonth
------------ --------------------- -----------------------
jillian0 45187.5136 2001-07-01 00:00:00.000
jillian0 7553.7239 2001-07-01 00:00:00.000
jillian0 8095.7863 2001-07-01 00:00:00.000
jillian0 1159.9768 2001-07-01 00:00:00.000
sum
---------------------
61997.0006
...etc...
Now, the documentation says that you can use ROLLUP to do the same thing but doesn't say how. You can get almost the same result, although nobody in their right minds would want it nowadays. A more useful report would give the monthly subtotals for each member of the sales team, the total for the month, and the grand total. Here is that more useful report, but you should be warned that the NULL values mean 'total' rather than unknown, and you would do well to use the GROUPING() function to do the fancy formatting at the application end of the process. The result is odd, since it has an order, and if you insert the results into a table, it is a good idea to put a row-order value in it.*/
SELECT name,
totalDue,
RIGHT(CONVERT(CHAR(11),Ordermonth,113),8),
SUM(totaldue) AS total FROM (
SELECT [name]=CONVERT(VARCHAR(12),RIGHT(HumanResources.Employee.LoginID, CHARINDEX('\',REVERSE(HumanResources.Employee.LoginID)+'\')-1)),
[orderMonth]=CONVERT(DATETIME,'1 '+RIGHT(CONVERT(CHAR(11),Sales.SalesOrderHeader.OrderDate,113),8)),
Sales.SalesOrderHeader.TotalDue
FROM HumanResources.Employee INNER JOIN
Sales.SalesPerson ON Sales.SalesPerson.SalesPersonID = HumanResources.Employee.EmployeeID INNER JOIN
Sales.SalesOrderHeader ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.SalesPersonID
)salesReport
GROUP BY ordermonth,name,totalDue
WITH rollup
/*
name totalDue total
------------ --------------------- -------- ---------------------
david8 974.0229 Jul 2001 974.0229
david8 4592.3356 Jul 2001 4592.3356
david8 10784.9873 Jul 2001 10784.9873
david8 19005.2087 Jul 2001 19005.2087
david8 56729.9942 Jul 2001 56729.9942
david8 NULL Jul 2001 92086.5487
garrett1 1737.6562 Jul 2001 1737.6562
garrett1 10319.3341 Jul 2001 10319.3341
garrett1 NULL Jul 2001 12056.9903
jillian0 1159.9768 Jul 2001 1159.9768
jillian0 7553.7239 Jul 2001 7553.7239
jillian0 8095.7863 Jul 2001 8095.7863
jillian0 45187.5136 Jul 2001 45187.5136
jillian0 NULL Jul 2001 61997.0006
...etc...
You'll see that there is an extra column for totals, but there is a certain logic to it.
The last lines look like this
tsvi0 NULL Jun 2004 309113.7562
NULL NULL Jun 2004 4573990.91
NULL NULL NULL 108266245.7018
The first one shows the total for tsvi0 for June 2004 as being 309113.7562
the next line shows the entire total sales for June as being 4573990.91
the final line shows the grand total being 108266245.7018
As a final thought, it is actually very easy to process a report like this to provide HTML or XHTML output to then render it on a browser, and print out from the browser. It is quick to do once you've got the SQL set up, and it looks great.
*/
