http://www.sqlservercentral.com/blogs/philfactor/2010/09/23/compute-now-it-is-going-what-else-will-do-it/

Printed 2014/04/18 12:48PM

COMPUTE: now it is going, what else will do it?

By Phil Factor, 2010/09/23

/*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.
*/


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.