SQL Staging Summary

--Test data CREATE TABLE TB ([Db-id] varchar (), ENTITY varchar (TEN), DATE varchar (ten), [Cust-no] int,amount decimal (10,2), tax Decimal (10,2)) Insert TB Select ' RCHQ ', ' 001 ', ' 2004-11-10 ', 200000,100.00,17.00union all select ' RCHQ ', ' 001 ', ' 2004-11-10 ', 200000,200.00,34.00union all select ' RCHQ ', ' 001 ', ' 2004-11-12 ', 200000,150.00,25.50union all select ' RCHQ ' , ' 002 ', ' 2004-11-10 ', 200000,100.00,17.00union all select ' RCHQ ', ' 002 ', ' 2004-11-10 ', 200000,200.00,34.00union all    Select ' RCHQ ', ' 002 ', ' 2004-11-12 ', 200000,150.00,25.50go--Query SELECT [Db-id],entity,date,[cust-no],amount,taxfrom (             SELECT [Db-id]=case when grouping ([Db-id]) =1 then ' total ' else [Db-id] end, entity=case When grouping ([Db-id]) =1 then "when" Grouping (ENTITY) =1 then ' subtotal ' else ENTITY end, DATE =case when grouping ([Db-id]) =1 then "when grouping (ENTITY) =1 then" when grouping (DAT      E) =1 Then ' subtotal ' else DATE end, [cust-no]=case       When grouping ([Db-id]) =1 then "when" Grouping (ENTITY) =1 then "when grouping (DATE) =1 Then ' ' When grouping ([cust-no]) =1 then ' subtotal ' else cast ([cust-no] as varchar) end, amount=sum (AMOUNT ), Tax=sum (tax), s1=grouping ([Db-id]), S2=[db-id], s3=grouping (ENTITY), s4=entity, s5=grouping (DATE), s6= DATE, S7=grouping ([cust-no]), S8=[cust-no] from TB GROUP by [Db-id],entity,date,[cust-no] with rollup havin G Grouping ([cust-no]) =1 UNION ALL SELECT [Db-id],entity,date,cast ([cust-no] as varchar), Amount,tax, s1=0,s2=[ Db-id], s3=0,s4=entity, S5=0,s6=date, S7=0,s8=[cust-no] from TB (a) Order by s1,s2,s3,s4,s5,s6,s7,s 8go--Delete test drop table tb/** DB-IDENTITYDATECUST-NOAMOUNTTAXRCHQ0012004-11-10200000100.0017.00RCHQ0012004-11-10200000200.0034.00RCHQ0012004-11-10 Subtotal 300 .0051.00rchq0012004-11-12200000150.0025.50rchq0012004-11-12 Subtotal 150.0025.50rchq001 Subtotal 450.0076.50rchq0022004-11-10200000100.0017.00RCHQ0022004-11-10200000200.0034.00RCHQ0022004-11-10 Subtotal 300.0051.00rchq0022004-11-12200000150.0025.50rchq0022004-11-12 Small Count 150.0025.50rchq002 Subtotal 450.0076.50RCHQ subtotal 900.00153.00 Total 900.00153.00*/

SQL Staging Summary