[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 438
  • Last Modified:

How to modify a SELECT statememt to add additional fields from the input table using SQL Server 2005?

select CASE WHEN (GROUPING(MthTitle) = 1)  THEN 'TOT:'
            WHEN (GROUPING(OfficeNumber) = 1) THEN 'SUB:'
            ELSE MthTitle
       END AS MthTitle
     , DollarTitle, OfficeNumber, sum(AcctValue) AcctValue
from tblSummMod td
group by MthTitle, DollarTitle, OfficeNumber with rollup
having GROUPING(DollarTitle) = 0 or GROUPING(MthTitle) = 1
order by DollarTitle DESC,
         CASE WHEN (GROUPING(MthTitle) = 1)  THEN char(255)
              ELSE td.DollarTitle
         END
       , CASE WHEN (GROUPING(OfficeNumber) = 1) THEN char(254)
              ELSE td.MthTitle
         END
       , AcctValue desc
---------------------------------
I am trying to modify the SQL Statement listed above that SELECTs from the following detail records from a table named tblSummMod with the following fields, types and values

MthTitle          DollarTitle           OffNumber    AcctValue    
nvarchar          nvarchar              nvarchar       decimal  
----------------   ------------------     ------------     ------------
Less4Months   W100K+Assets     104               $4000.00
Less4Months   W100K+Assets     102               $3000.00
Less4Months   W100K+Assets     107               $5000.00
Less4Months   W100K+Assets     109               $2000.00
Less4Months   W50K+Assets       101               $4000.00
Less4Months   W50K+Assets       103               $7000.00
Less4Months   W50K+Assets       106               $5000.00
Less4Months   W50K+Assets       107               $2000.00
Less4Months   W25K+Assets       101               $8000.00
Less4Months   W25K+Assets       103               $7000.00
Less4Months   W25K+Assets       106               $4000.00
Less4Months   W25K+Assets       107               $1000.00

and the SQL Statement generates the following output records using SQL Server 2005.
I want to keep the output in this particular order of row sequence because the user wants to see the records for the dollar amounts in DESCENDING ORDER by the field AcctValue within the fields MnthTitle (value never changes)
and DollarTitle range groupings.

I export the result set of output records to an Excel file as a report with Subtotal and Total records.

MthTitle          DollarTitle           OffNumber    AcctValue    

nvarchar          nvarchar              nvarchar       decimal  

Less4Months   W100K+Assets     107               $ 5000.00
Less4Months   W100K+Assets     104               $ 4000.00
Less4Months   W100K+Assets     102               $ 3000.00
Less4Months   W100K+Assets     109               $ 2000.00
SUB:                    W100K+Assets                           $14000.00
Less4Months   W50K+Assets       103               $ 7000.00
Less4Months   W50K+Assets       106               $ 5000.00
Less4Months   W50K+Assets       101               $ 4000.00
Less4Months   W50K+Assets       107               $ 2000.00
SUB:                    W50K+Assets                            $18000.00
Less4Months   W25K+Assets       101               $ 8000.00
Less4Months   W25K+Assets       103               $ 7000.00
Less4Months   W25K+Assets       106               $ 4000.00
Less4Months   W25K+Assets       107               $ 1000.00
SUB:                    W25K+Assets                             $20000.00
TOT:                                                                              $52000.00

The modification is to add the additional fields noted in CAPTIAL LETTERS to the original SELECT statement as follows. These ADDITIONAL fields are containted in the input table.

MnthTitle  DollarTitle  OffNumber  CUSTNUMBER  FIRSTNAME  LASTNAME ADDR1  Acct Value
0
zimmer9
Asked:
zimmer9
1 Solution
 
lwadwellCommented:
Something like:
;with tblSummMod as (
select 'Less4Months' as MthTitle, 'W100K+Assets' as DollarTitle, '104' as OffNumber, 4000.00 as AcctValue, 
       12345 as CustNumber, 'John' as FirstName,  'Jones' as LastName, 'Jakarta' as Addr1                 union all 
select 'Less4Months' as MthTitle, 'W100K+Assets' as DollarTitle, '102' as OffNumber, 3000.00 as AcctValue, 
       12345 as CustNumber, 'John' as FirstName,  'Jones' as LastName, 'Jakarta' as Addr1                 union all 
select 'Less4Months' as MthTitle, 'W100K+Assets' as DollarTitle, '107' as OffNumber, 5000.00 as AcctValue, 
       12346 as CustNumber, 'Mary' as FirstName,  'Williams' as LastName, 'Wellington' as Addr1           union all 
select 'Less4Months' as MthTitle, 'W100K+Assets' as DollarTitle, '109' as OffNumber, 2000.00 as AcctValue, 
       12346 as CustNumber, 'Mary' as FirstName,  'Williams' as LastName, 'Wellington' as Addr1           union all 
select 'Less4Months' as MthTitle, 'W50K+Assets ' as DollarTitle, '101' as OffNumber, 4000.00 as AcctValue, 
       12347 as CustNumber, 'Fred' as FirstName,  'Bourke' as LastName, 'Sydney' as Addr1                 union all 
select 'Less4Months' as MthTitle, 'W50K+Assets ' as DollarTitle, '103' as OffNumber, 7000.00 as AcctValue, 
       12347 as CustNumber, 'Fred' as FirstName,  'Bourke' as LastName, 'Sydney' as Addr1                 union all 
select 'Less4Months' as MthTitle, 'W50K+Assets ' as DollarTitle, '106' as OffNumber, 5000.00 as AcctValue, 
       12348 as CustNumber, 'Tony' as FirstName,  'Ng' as LastName, 'Prague' as Addr1                     union all 
select 'Less4Months' as MthTitle, 'W50K+Assets ' as DollarTitle, '107' as OffNumber, 2000.00 as AcctValue, 
       12348 as CustNumber, 'Tony' as FirstName,  'Ng' as LastName, 'Prague' as Addr1                     union all 
select 'Less4Months' as MthTitle, 'W25K+Assets ' as DollarTitle, '101' as OffNumber, 8000.00 as AcctValue, 
       12349 as CustNumber, 'Jane' as FirstName,  'Singh' as LastName, 'London' as Addr1                  union all 
select 'Less4Months' as MthTitle, 'W25K+Assets ' as DollarTitle, '103' as OffNumber, 7000.00 as AcctValue, 
       12349 as CustNumber, 'Jane' as FirstName,  'Singh' as LastName, 'London' as Addr1                  union all 
select 'Less4Months' as MthTitle, 'W25K+Assets ' as DollarTitle, '106' as OffNumber, 4000.00 as AcctValue, 
       12350 as CustNumber, 'Sunshine' as FirstName,  'Smith' as LastName, 'New York' as Addr1            union all 
select 'Less4Months' as MthTitle, 'W25K+Assets ' as DollarTitle, '107' as OffNumber, 1000.00 as AcctValue, 
       12350 as CustNumber, 'Sunshine' as FirstName,  'Smith' as LastName, 'New York' as Addr1                 
)
select CASE WHEN (GROUPING(MthTitle) = 1)  THEN 'TOT:'
            WHEN (GROUPING(OffNumber) = 1) THEN 'SUB:'
            ELSE MthTitle
       END AS MthTitle
     , DollarTitle, OffNumber, CustNumber, FirstName, LastName, Addr1
     , sum(AcctValue) AcctValue
from tblSummMod td
group by MthTitle, DollarTitle, OffNumber, CustNumber, FirstName, LastName, Addr1 with rollup
having (GROUPING(OffNumber) = 1 and GROUPING(DollarTitle) = 0)
    or GROUPING(DollarTitle)
       + GROUPING(CustNumber)
       + GROUPING(FirstName)
       + GROUPING(LastName)
       + GROUPING(Addr1) = 0 
    or GROUPING(MthTitle) = 1 
order by CASE WHEN (GROUPING(MthTitle) = 1)  THEN char(255)
              ELSE td.DollarTitle
         END
       , CASE WHEN (GROUPING(OffNumber) = 1) THEN char(254)
              ELSE td.MthTitle
         END
       , AcctValue desc

Open in new window

I used a CTE to generate my test data ... you can remove.
0
 
zimmer9Author Commented:
Awesome!!!!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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