Case SQL如果日期之间没有记录

Question

我是SQL的新手，正在处理一个案例。我想说明的是，如果一个账户（account_ID）在current_date-302和current_date-62之间没有记录（ON billing_ID），那么用"1"标记

以下查询：

提前感谢

SELECT 
    billing_date_local_time
    ,account_id
    ,contract_owner_name
    ,date_first_feature_partner
    ,deal_starts_at
    ,contract_id
    ,new_partner_type
    ,sum(voucher_sold) AS Vouchers
    ,sum(gross_bookings_local) AS GB
    ,sum(gross_revenue_local) AS GR
    ,is_G2
    ,Case when billing_date_local_time between current_date-302 and current_date-62 = 0 THEN 'YES' ELSE 'NO'  End
FROM EMEA_ANALYTICS.eu_deal_flat 
WHERE 
      country_id = 206 
  and billing_date_local_time between current_date-400 
  and current_date-2
GROUP BY 1,2,3,4,5,6,10,11

Answer 1

您需要执行相关的子查询；像这样的东西：

select 
a.billing_date_local_time
,a.account_id
,...
, CASE WHEN EXISTS (SELECT * FROM EMEA_ANALYTICS.eu_deal_flat b WHERE a.account_id = b.account_id AND b.billing_date_local_time between current_date-302 and current_date-62 ) THEN 'YES' ELSE 'NO' END
from 
FROM EMEA_ANALYTICS.eu_deal_flat a
WHERE ...

Answer 2

您需要应用这样的聚合函数：

min(case when billing_date_local_time 
              between current_date-302 and current_date-62
         then 0
         else 1
    end)