Is there a way to write a SQL Statement to create subtotals, in addition to the detail records, using SQL Server 2008?

I have a table named tblRange and create a query to geneate the following output:

SELECT MthTitl As [Date Range], DFrC as [From], DToC As [To], DollTitl As [Dollar Range],
CountOfCustomerNumber As [Num Of Accts], SumOfAcctValue As [Acct Value], MthF As [# Months],
DFr As [From Date ] FROM dbo.tblRange
ORDER By DFrC DESC, DollTitl DESC

result:

Date Range                   From              To                    Dollar Range    Num Of Accts    Acct Value  
--------------------------       -------------        --------------       -------------------  --------------------    ---------------
LESS THAN 6 MOS       2014-10-01    2015-03-01    WITH $100K                948           678648.46
LESS THAN 6 MOS       2014-10-01    2015-03-01    WITH $  50K                662           456456.53      
6 MOS TO 1 YEAR        2014-04-01    2014-09-01    WITH $ 100K               416           234245.54
6 MOS TO 1 YEAR        2014-04-01    2014-09-01    WITH $   25K               473           145457.76
1 TO 2 YEARS               2013-04-01    2014-03-01    WITH $ 100K               598           854754.65          
1 TO 2 YEARS               2013-04-01    2014-03-01    WITH $   50K              1084          746953.64
1 TO 2 YEARS               2013-04-01    2014-03-01    WITH $   10K                861          357735.22
2 TO 3 YEARS               2012-04-01    2013-03-01    WITH $100K                598           432864.35
2 TO 3 YEARS               2012-04-01    2013-03-01    WITH $  50K                342           865676.54
3 PLUS YEARS              1931-12-01    2012-03-01    WITH $  50K                113           253743.64
3 PLUS YEARS              1931-12-01    2012-03-01    WITH $  10K                475           463435.56
3 PLUS YEARS              1931-12-01    2012-03-01    WITH $    1K                735           356765.64
OTHER                          NULL              NULL               WITH $  50K                375           575654.46
OTHER                          NULL              NULL               WITH $  10K                153           453644.68

Is there another query that could be executed after this one, based on the above result, to create the following output which:

1) shows SUBTOTALS for each of the distinct "Date Range" values for the 2 fields
     "Num Of Accts"   and   "Acct Value"  

2) replaces the value in the "From" field for the Data Range "3 PLUS YEARS"
from its current value of "1931-12-01"        to a revised value of "EARLIEST"

Date Range                   From              To                    Dollar Range    Num Of Accts    Acct Value  
--------------------------       -------------        --------------       -------------------  --------------------    ---------------
LESS THAN 6 MOS       2014-10-01    2015-03-01    WITH $100K                948           678648.46
LESS THAN 6 MOS       2014-10-01    2015-03-01    WITH $  50K                662           456456.53      
                                                                                                                           1610         1135104.99
6 MOS TO 1 YEAR        2014-04-01    2014-09-01    WITH $ 100K               416           234245.54
6 MOS TO 1 YEAR        2014-04-01    2014-09-01    WITH $   25K               473           145457.76
                                                                                                                             889           379703.30
1 TO 2 YEARS               2013-04-01    2014-03-01    WITH $ 100K               598           854754.65          
1 TO 2 YEARS               2013-04-01    2014-03-01    WITH $   50K             1084           746953.64
1 TO 2 YEARS               2013-04-01    2014-03-01    WITH $   10K                861          357735.22
                                                                                                                           2543         1959443.51
2 TO 3 YEARS               2012-04-01    2013-03-01    WITH $100K                598           432864.35
2 TO 3 YEARS               2012-04-01    2013-03-01    WITH $  50K                342           865676.54
                                                                                                                            940          1298540.89
3 PLUS YEARS              EARLIEST        2012-03-01    WITH $  50K                113           253743.64
3 PLUS YEARS              EARLIEST        2012-03-01    WITH $  10K                475           463435.56
3 PLUS YEARS              EARLIEST        2012-03-01    WITH $    1K                735           356765.64
                                                                                                                          1323         1073944.84
OTHER                          NULL              NULL               WITH $  50K                375           575654.46
OTHER                          NULL              NULL               WITH $  10K                153           453644.68
                                                                                                                            528         1029299.14
zimmer9Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Is there a way to write a SQL Statement to create subtotals
You can create subtotals in T-SQL using ROLLUP,  but I really recommend leaving cosmetic displays of subtotals to whatever reporting app is rendering this data to users.

T-SQL is not optimzed for cosmetic formatting of subtotals, and doing this in the return set will mess up the ability to sort the data in the report.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>2) replaces the value in the "From" field for the Data Range "3 PLUS YEARS"
from its current value of "1931-12-01"        to a revised value of "EARLIEST"
Yes, using a subquery,  but if this is done in T-SQL than that column becomes a varchar and not date, and will affect sorting.

Recommend again doing this in the report, using something like an SSRS expression of
=IIF(Fields![From].Value='1931-12-01', 'EARLIEST', Fields![From].Value)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.