在T-SQL中可能使用更优雅的语句代替子select

Question

我有以下代码，该代码计算在进入特定医院([程序])12个月后，唯一客户[ClientWHID]的数量和这些客户无家可归时的天数，

我想知道，如果有更优雅的方式来写这段代码?例如，要替换括号中的子select，请使用用CASE语句?或者在这种情况下是不可能的?

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]

Answer 1

由于您不使用除EHRProgramWHID,ProgramName,ClientWHID以外的CTE部分的任何列，因此不需要返回它们。

没有样本数据我不能检查这个，但尝试:

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]