Sql-server – How to retrieve subtotal for order lines (rollup does not exist in sql 2000)

crystal-reportssql serversql-server-2000

Currently in a crystal report I have each order grouped together where the user is displayed the order lines in the orders group. I have made a running total field for a subtotal for all of the order lines prices. But I wan't to make a parameter for the end user to select whether the price is > or < than a specific amount. And what I thought may be the best solution is calculate the subtotal in the stored procedure and pass it to the report to build the parameter off of. But it looks like SQL Server 2000 does not contain the ROLLUP function in later versions.

Best Answer

Figured it out:

select TS.Order_no, part_no, cost_per_line, 
       case when Order_no = (select top 1 Order_no from #tempsales 
                           where cust_po = TS.cust_po
                          order by Order_no asc)
            then (select sum(cost_per_line)
                     from #tempsales
                     where Order_no <= TS.Order_no
                        and cust_po = TS.cust_po)
            else '0' end as 'Sub Total',
       case when Order_no = (select top 1 Order_no from #tempsales 
                           where cust_po = TS.cust_po
                          order by Order_no asc)
            then (select CAST(ROUND(SUM(cost_per_line), 2, 1) AS DECIMAL(18, 2))
                      from #tempsales) 
             else '0' end as 'Grand Total'
from #tempsales TS
Related Question