tsql - Possible use of a more elegant statement instead of sub select, in T-SQL

Question

I have the following code, which calculates # of unique Clients [ClientWHID] and # of days for these clients when they were homeless, 12 months after entering specific hospital(s) ([Program]), plz see below.

I am wondering, if there is more elegant way to write this code? For example, to replace my sub select in parentheses cte with CASE statement? Or is it not possible in this situation?

     DECLARE @BOP date = '07/01/2019'
     DECLARE @EOP date = '06/30/2020'
     DECLARE @EHRProgramWHID int = -1;  --  54500457 = SM AOT; 54200471 = SM FSP

   SELECT 
        cte.[EHRProgramWHID]
       ,cte.[ProgramName]
       ,COUNT( DISTINCT cte.[ClientWHID]) AS [# Homeless Clients 12 Mo after Enrollment]
       ,SUM (cte.[# Homeless Days]) AS [# Homeless Days 12 Mo after Enrollment]

  FROM
       (
         SELECT 
               epi.[EHRProgramWHID]
              ,prg.[ProgramName]
              ,epi.[Domain]
              ,epi.[Subdomain]
              ,epi.[ClientWHID]
              ,epi.[BeginDate]
              ,epi.[EndDate]
              ,IIF(epi.[BeginDate] < @BOP, @BOP, epi.[BeginDate]) AS [Upd_BeginDate]
              ,IIF(epi.[EndDate] > @EOP, @EOP, epi.[EndDate]) AS [Upd_EndDate]
              ,DATEDIFF(day, IIF(epi.[BeginDate] < @BOP, @BOP, epi.[BeginDate]), 
                             IIF(epi.[EndDate] > @EOP, @EOP, epi.[EndDate])) AS [# Homeless Days]

         FROM
              WHMHSA.[DomainStatusEpisodes] epi
              INNER JOIN [Dimension].[Program] prg ON epi.[EHRProgramWHID] = prg.[WHID]

         WHERE 
             NOT(  ( (epi.BeginDate < @BOP) AND (epi.EndDate < @BOP) )  OR 
                   ( (epi.BeginDate > @EOP) AND (epi.EndDate > @EOP) )  )
             AND (epi.Domain = 'Residential')
             AND (epi.Subdomain = 'Homeless')
             AND (epi.EHRProgramWHID IN (54500457, 54200471))
  
        ) cte


GROUP BY
        cte.[EHRProgramWHID]
       ,cte.[ProgramName]
   
ORDER BY
        cte.[ProgramName]
  

question from:https://stackoverflow.com/questions/65931400/possible-use-of-a-more-elegant-statement-instead-of-sub-select-in-t-sql

Answer 1

Since you do not use any of the columns of the CTE part other than EHRProgramWHID , ProgramName , ClientWHID then there is no need to return them.

Without sample data I can't check this, but try:

 DECLARE @BOP date = '07/01/2019'
 DECLARE @EOP date = '06/30/2020'
 DECLARE @EHRProgramWHID int = -1;  --  54500457 = SM AOT; 54200471 = SM FSP

 SELECT 
    epi.[EHRProgramWHID]
   ,prg.[ProgramName]
   ,COUNT( DISTINCT epi.[ClientWHID]) AS [# Homeless Clients 12 Mo after Enrollment]
   ,SUM (DATEDIFF(day, IIF(epi.[BeginDate] < @BOP, @BOP, epi.[BeginDate]), 
                         IIF(epi.[EndDate] > @EOP, @EOP, epi.[EndDate]))) AS [# Homeless Days 12 Mo after Enrollment]

FROM WHMHSA.[DomainStatusEpisodes] epi
     INNER JOIN [Dimension].[Program] prg ON epi.[EHRProgramWHID] = prg.[WHID]
WHERE 
     NOT(  ( (epi.BeginDate < @BOP) AND (epi.EndDate < @BOP) )  OR 
           ( (epi.BeginDate > @EOP) AND (epi.EndDate > @EOP) )  )
     AND (epi.Domain = 'Residential')
     AND (epi.Subdomain = 'Homeless')
     AND (epi.EHRProgramWHID IN (54500457, 54200471))
GROUP BY
     epi.[EHRProgramWHID]
    ,prg.[ProgramName]

ORDER BY
     prg.[ProgramName]