SELECT a.BIZ_DATE,2 as db_id,A.store_code,a.district_code,
 a.order_method,a.cus_type,'' as sales_type,'' as sales_channel,
COUNT(case when Order_Amount>0 then a.trans_no end) as trans_cnt,
 SUM(is null(a.people_number,0))  as people_number, 
SUM(is null(a.order_amount,0)) as order_amount,
SUM(is null(A.Sales_Amount,0)) as sales_amount,
SUM(is null(a.discount_amount,0)) as discount_amount,
SUM(IS NULL(e.amt,0)) as amt,
SUM(IS NULL(e.amt_vld,0)) as amt_vld, 
SUM(IS NULL(e.amt_uvld,0)) as amt_uvld,
SUM(IS NULL(e.amt_vld,0)) as amt_all
 from (
select a.BIZ_DATE,A.store_code,a.district_code,
 a.order_method,a.cus_type,a.trans_no,
case when SUM(is null(A.Sales_Amount,0)) =0 then 0 else SUM(is null(a.people_number,0)) end as people_number, 
SUM(is null(a.order_amount,0)) as order_amount,
SUM(is null(A.Sales_Amount,0)) as sales_amount,
SUM(is null(a.discount_amount,0)) as discount_amount
 FROM BI005 A 
 where a.biz_date>='2019-08-27 00:00:00'
 and a.biz_date<='2019-08-21 00:00:00'
group by  a.BIZ_DATE,A.store_code,a.district_code,
 a.order_method,a.cus_type,a.trans_no)a
left join 
(select b.trans_no,b.store_code,b.biz_date,b.district_code,
SUM(is null(case when d.flag='1' then b.amount end,0)) amt_vld,  
SUM(is null(case when d.flag='0' then b.amount end,0)) amt_uvld, 
SUM(is null(b.amount,0) ) amt
 from BI007 b 
left join 
(select * from dhbi_pccode_all) d
on b.store_code = d.store_code
and b.tender_code = d.tender_code
where b.biz_date >='2019-08-27 00:00:00'
and b.biz_date<='2019-08-21 00:00:00'
group by b.trans_no,b.store_code,b.biz_date,b.district_code
) e
on a.trans_no = e.trans_no
and a.store_code = e.store_code
and a.biz_date = e.biz_date 
and a.district_code = e.district_code
GROUP BY a.BIZ_DATE,A.store_code,a.district_code,a.order_method,a.cus_type