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:

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.