How to replace a value in a column based on the value in another column when using a CASE statement with 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

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]
zimmer9Asked:
Who is Participating?
 
PortletPaulfreelancerCommented:
I think this is what you asking for:

CASE
      WHEN is_total_only = 0 AND [Date Range] = '3 PLUS YEAR' THEN 'Earliest'
      WHEN is_total_only = 0 THEN CONVERT(varchar(10), [From], 120)
      ELSE ''
END AS [From],

a case expression is evaluated top to bottom (well in order of the conditions), as soon as a condition is met the case expression returns that value and the remaining further conditions are not considered.

so, here we test for both  is_total_only = 0 AND [Date Range] = '3 PLUS YEAR'
if a row does have both of those values, this condition is met and the case expression returns 'Earliest'
other conditions in the case expression are now ignored for that row
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.

All Courses

From novice to tech pro — start learning today.