We help IT Professionals succeed at work.
Get Started

How to replace a value in a column based on the value in another column when using a CASE statement with SQL Server 2008?

zimmer9
zimmer9 asked
on
120 Views
Last Modified: 2015-03-07
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

I use the following Select statement with CASE to create subtotals.

How would you modify this SELECT statement as shown below with a CASE statement to replace WHATEVER value appears in the 2nd column titled "DFrC as [From]"  with the LITERAL VALUE 'Earliest
ONLY WHEN the 1st column titled "Date Range" has the value "3 PLUS YEARS"        

Thus, in this example, the value 1931-12-01 would be replaced by the LITERAL VALUE "Earliest".
However, the value will not always be 1931-12-01 so you just can't substitute 1931-12-01 with the value Earliest.


 SELECT
      CASE WHEN is_total_only = 0 THEN [Date Range] ELSE '' END AS [Date Range],
      CASE WHEN is_total_only = 0 THEN CONVERT(varchar(10), [From], 120) ELSE '' END AS [From],
      CASE WHEN is_total_only = 0 THEN CONVERT(varchar(10), [To], 120) ELSE '' END AS [To],
      CASE WHEN is_total_only = 0 THEN [Dollar Range] ELSE '' END AS [Dollar Range],
      [Num Of Accts],
      [Acct Value]
  FROM (
      SELECT
        0 AS is_total_only,
          MthTitl As [Date Range],
          DFrC as [From],
         DToC As [To], DollTitl As [Dollar Range],
        CountOfCustomerNumber As [Num Of Accts], SumOfAcctValue As [Acct Value]
      FROM tblRange tr
      UNION ALL
      SELECT
          1 AS is_total_only,
          MthTitl AS [Date Range],
         MIN(DFrC) AS [From],
          CAST('19000101' AS datetime) AS [To], '' AS [Dollar Range],
          SUM(CountOfCustomerNumber) AS CountOfCustomerNumber, SUM(SumOfAcctValue) AS SumOfAcctValue
      FROM tblRange
      GROUP BY MthTitl
  ) AS derived
ORDER By derived.[From] DESC, is_total_only, derived.[Dollar Range]
Comment
Watch Question
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE