• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 76
  • Last Modified:

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
0
zimmer9
Asked:
zimmer9
  • 2
1 Solution
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now