teradata - 从 3 个表中派生 2 个字段 SPEED_UPGRADE 和 TV_PACKAGE

Question

我需要从 3 个表中派生 2 个字段 SPEED_UPGRADE 和 TV_PACKAGE。我在下面写了代码。我没有得到介于 start_dt 和 END_DT 之间的“SPEED”和“TV”的正确计数。而是让我计算所有“速度”和“电视”。请优化我的查询。

select <few columns>,
count (case WHEN Cast('2021-11-09 00:00:00' AS TIMESTAMP(0)) 
              BETWEEN a.START_DT AND Coalesce(a.END_DT,Cast('9999-12-31 00:00:00' AS TIMESTAMP FORMAT 'Y4-MM-DDBHH:MI:SS')) AND b.VOUCHER_TYPE_CD='SPEED' 
            then 1 
            ELSE 0
       end) SPEED_UPGRADE,
count(case WHEN Cast('2021-11-09 00:00:00' AS TIMESTAMP(0)) 
             BETWEEN a.START_DT AND Coalesce(a.END_DT,Cast('9999-12-31 00:00:00' AS TIMESTAMP FORMAT 'Y4-MM-DDBHH:MI:SS')) AND b.VOUCHER_TYPE_CD='TV' 
           then 1
           ELSE 0
       end) TV_PACKAGE


FROM
(sel * from P0_view.edw_v_fct_subscriber_household_base where subscriber_status_cd ='Active' and billed_customer_id ='-1' and household_id >0 and household_base_dt='2021-11-09' ) f
right join P0_VIEW.EDW_V_FCT_FIXED_VOUCHER_REDEEMED a on f.CUSTOMER_ID=a.CUSTOMER_ID
left join P0_VIEW.EDW_V_DIM_FIXED_VOUCHER b on a.FIXED_VOUCHER_ID = b.FIXED_VOUCHER_ID
group by 1,2,3,4,5,6,7,8,9 ) q ;

标签： teradata