如何影响具有多个状态的where子句中的单个状态

Question

以下是我的查询，它返回从年至日的策略计数。我想知道如何处理一个只会影响“活跃”状态的陈述。

select distinct 
count(pol3.CT_ID + pol3.CT_NSID) as 'YTD', 
pol3.ct_status 
from contract pol3 
where (pol3.CT_Status = 'Quote' or pol3.ct_status='Active' or pol3.ct_status='Declined') 
and Year(pol3.CT_Subscription_Date)>= datediff(year,(DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)), getdate()) 
group by pol3.ct_status 

下面是电流输出：

我试图做

select distinct 
count(pol3.CT_ID + pol3.CT_NSID) as 'YTD', 
pol3.ct_status 
from contract pol3 
where (pol3.CT_Status = 'Quote' or pol3.ct_status='Active' or pol3.ct_status='Declined') 
and CT_ORIGINAL_QUOTE_ID is not null 
and CT_ORIGINAL_QUOTE_NSID is not null 
and Year(pol3.CT_Subscription_Date)>= datediff(year,(DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)), getdate()) 
group by pol3.ct_status 

这显然是错误的，因为它针对所有三种状态。我如何才能让ct_original_ *只针对活动状态？任何线索或指针将不胜感激。

Answer 1

在SELECT中使用CASE语句返回1或0，然后返回SUM值。

SELECT pol3.ct_status, SUM(CASE 
    WHEN ct_status IN ('Declined','Quote') THEN 1 // all of these should count as one policy 
    WHEN ct_status = 'Active' and CT_ORIGINAL_QUOTE_ID is not null and CT_ORIGINAL_QUOTE_NSID is not null THEN 1 // if data in ID and NSID and active count it 
    ELSE 0 // all others count as nothing 
    END) 
from contract pol3 
where (pol3.CT_Status = 'Quote' or pol3.ct_status='Active' or pol3.ct_status='Declined') 
and Year(pol3.CT_Subscription_Date)>= datediff(year,(DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)), getdate()) 
group by pol3.ct_status