GROUP BY with nested case expression – is there better way?

SQL server 2012

I am taking fees received and multiplying them by different factors based on how long the Client has been a Client. The group by clause is fairly straight forward. However, my select gets awkward when I want to use this criteria in different ways:

select mp.professionals
    ,case when sl.stmndate < dateadd(year, 3, m.qClientOpenDate) then 'New' else 'Old' end age -- straight forward
    ,case (case when sl.stmndate < dateadd(year, 3, m.qClientOpenDate) then 'New' else 'Old' end) -- nested case
        when 'New' then sum(fees) * 0.5
        when 'Old' then sum(fees) * 0.25
        else 0
    end Credit
    ,case (case when sl.stmndate < dateadd(year, 3, m.qClientOpenDate) then 'New' else 'Old' end) -- nested case
        when 'New' then 'Welcome!'
        when 'Old' then 'Thank you for being a long-time Client!'
    end Greeting
from mattersprofessionals mp
inner join matters m on m.matters = mp.matters
inner join stmnledger sl on sl.matters = mp.matters
group by mp.professionals, case when sl.stmndate < dateadd(year, 3, m.qClientOpenDate) then 'New' else 'Old' end

I suppose I should mention this is a simplified version of my actual case statements.

I was hoping there was a way to group by sl.stmndate < dateadd(year, 3, m.qClientOpenDate) as a boolean or something so I don’t have to do the nested case expressions.

I know I could do a sub-query on the basic case and then do more case expressions in the outer query. But that’s just rearranging the same nested case concept.

Answer

Technically I think the answer to my question is “No”. There isn’t a way to group by the boolean portion of a case statement so it can be used in various ways within the select.

I upvoted GMB’s and Martin Smith’s answers as they are helpful and informative. I am playing with the cross apply, a learning experience for sure.

Advertisement

Answer