select f.新增日期,a.*,e.其他卡源,b.新增激活不同,c.其他卡源并且激活异常,d.总数 from (select HomeAreaID as 归属中心,Reseau as 网格 from FactIncreaseCustomer group by IncreaseDate,HomeAreaID,Reseau) a
left join (select IncreaseDate,HomeAreaID,Reseau,count(IncreaseStatus) as 其他卡源 from FactIncreaseCustomer where IncreaseStatus=1 group by IncreaseDate,HomeAreaID,Reseau ) e on f.新增日期=e.IncreaseDate and a.归属中心=e.HomeAreaID and a.网格=e.Reseau
left join (select IncreaseDate,HomeAreaID,Reseau,count(IncreaseStatus) as 新增激活不同 from FactIncreaseCustomer where IncreaseStatus=2 group by IncreaseDate,HomeAreaID,Reseau ) b on f.新增日期=b.IncreaseDate and a.归属中心=b.HomeAreaID and a.网格=b.Reseau
left join (select IncreaseDate,HomeAreaID,Reseau,count(IncreaseStatus) as 其他卡源并且激活异常 from FactIncreaseCustomer where IncreaseStatus=4 group by IncreaseDate,HomeAreaID,Reseau ) c on c.IncreaseDate=f.新增日期 and c.HomeAreaID=a.归属中心 and c.Reseau=a.网格
left join (select IncreaseDate,HomeAreaID,Reseau,count(IncreaseStatus) as 总数 from FactIncreaseCustomergroup by IncreaseDate,HomeAreaID,Reseau ) d on f.新增日期=d.IncreaseDate and a.归属中心=d.HomeAreaID and a.网格=d.Reseau
left join (select distinct top 30 IncreaseDate as 新增日期,IncreaseDate,HomeAreaID,Reseau from FactIncreaseCustomergroup by IncreaseDate,HomeAreaID,Reseau ) f on f.新增日期=f.IncreaseDate and a.归属中心=f.HomeAreaID and a.网格=f.Reseau
order by f.新增日期 Desc
