DECLARE @tab TABLE(OrderID int, OrderDate Date, OrderAmt DEC(5,2))
INSERT @tab
SELECT 1, '10/11/2003', 10.50 union all
SELECT 2 , '10/11/2003', 11.50 union all
SELECT 3 , '10/11/2003', 1.25 union all
SELECT 4 , '10/12/2003', 100.57 union all
SELECT 5 , '10/12/2003', 19.99 union all
SELECT 6 , '10/13/2003', 47.14 union all
SELECT 7 , '10/13/2003', 10.08 union all
SELECT 8 , '10/13/2003', 7.50 union all
SELECT 9 , '10/13/2003', 9.50
SELECT CASE WHEN OrderId= 0 AND OrderDate IS NULL THEN 'Grand Total'
WHEN ORDERID= 0 THEN 'Sub Total' ELSE '' END as ReportHeading, OrderID , ISNULL(OrderDate, '99991231') OrderDate, OrderAmt
FROM (SELECT * FROM @tab UNION
SELECT 0, OrderDate,SUM(OrderAmt)
FROM @tab
GROUP BY OrderDate WITH ROLLUP)T
ORDER BY OrderDate, CASE WHEN OrderId= 0 AND OrderDate IS NULL THEN 3
WHEN ORDERID= 0 THEN 2 ELSE 1 END ;
Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif
Cpatil1000, if this helps please login to Mark As Answer. | Alert Moderator