Calculation within a CASE Expression
15:09 16 Aug 2018

I am trying to include a 4th "Program" within my case expression that would be a combination of ALL programs added together since there is no Table ID that identifies for ALL (i.e. pp.id=2 + p.id=3 + p.id=4 = All). Is a CASE expression able to achieve this? COALESCE? A nested SELECT?

DECLARE @date datetime = CONVERT(date, getdate())

Select Count(Distinct fm.MEMBER_ID) As 'Count',
      Cast(Cast(DatePart(YEAR, dd.DATE) As VARCHAR) + '/' + Cast(DatePart(MONTH,
      dd.DATE) As VARCHAR) + '/01' As DATE) As 'Month',
      Case When p.ID = 2 Then 'Program1' When p.ID = 3 Then 'Program2'
        When p.ID = 4 Then 'Program3' End As 'Program'
    From FACT_MEMBER_MONTH_START fm
      Join DIM_COVERAGE c On fm.MEMBER_ID = c.MEMBER_ID
      Join DIM_PROGRAM p On c.PROGRAM_ID = p.ID
      Join DIM_DATE dd On fm.MONTH_ID = dd.MONTH_ID
      Join DIM_MEMBER dm On fm.MEMBER_ID = dm.ID
      Join DIM_INDIVIDUAL DI On fm.MEMBER_ID = DI.ID
      Join DIM_LIVING_SITUATION dls On fm.LIVING_SITUATION_ID = dls.ID
    Where dd.DATE Between c.EFFECTIVE_DATE And c.EXPIRATION_DATE And (dls.ID = 5
        Or dls.ID = 6) and (dd.DATE between DATEADD(MM, -18, @date) and @date)
    Group By Cast(Cast(DatePart(YEAR, dd.DATE) As VARCHAR) + '/' +
      Cast(DatePart(MONTH, dd.DATE) As VARCHAR) + '/01' As DATE),
      Case When p.ID = 2 Then 'FC' When p.ID = 3 Then 'PACE'
        When p.ID = 4 Then 'Part' End
 ORDER BY Month desc

Results:

Sample Results

End goal would be a "4th Program" that is just a summation of the 3. This is to be used in Logi Analytics to develop multiple charts off one query that can be filtered using these separate programs.

sql sql-server