---报表:城市、截止开通数、上周开通数、上上周开通数
--第一版
SELECT cc.CityName ,
SUM(t1.storeCnt) AS totalOpenCnt ,
SUM(CASE t2.flag
WHEN 1 THEN t1.storeCnt
ELSE 0
END) AS weekOpenCnt ,
SUM(CASE t3.flag
WHEN 1 THEN t1.storeCnt
ELSE 0
END) AS preWeekOpenCnt
FROM dbo.RMS_CompanyList cc
LEFT JOIN ( SELECT c.CompanyNo ,
COUNT(c.CompanyNo) storeCnt
FROM dbo.CRM_Company c
LEFT JOIN dbo.CRM_CompanyStore cs ON c.id = cs.CompanyId
WHERE cs.IsDelete = 0
AND C.IsDelete = 0
GROUP BY c.CompanyNo
) t1 ON t1.CompanyNo = cc.CompanyNo
LEFT JOIN ( SELECT 1 AS flag ,
companyNo
FROM dbo.RMS_CompanyList
WHERE RowDate >= '2017-06-16'
AND RowDate <= '2017-06-22'
) t2 ON t2.CompanyNo = cc.CompanyNo
LEFT JOIN ( SELECT 1 AS flag ,
companyNo
FROM dbo.RMS_CompanyList
WHERE RowDate >= DATEADD(day, -7, '2017-06-16')
AND RowDate <= DATEADD(day, -7, '2017-06-22')
) t3 ON t3.CompanyNo = cc.CompanyNo
WHERE RowDate <= '2017-07-11'
AND CompanyName != '房屋管家'
AND CompanyName NOT LIKE '%实惠%'
AND CompanyName NOT LIKE '%测试%'
AND CityName IN ( '上海', '杭州', '郑州', '重庆', '武汉', '合肥', '济南', '南京' )
GROUP BY CityName
--第二版
SELECT cc.CityName ,
ISNULL(SUM(1), 0) AS totalOpenCnt ,
ISNULL(SUM(CASE WHEN RowDate >= '2017-06-16'
AND RowDate <= '2017-06-22' THEN 1
ELSE 0
END), 0) AS weekOpenCnt ,
ISNULL(SUM(CASE WHEN RowDate >= DATEADD(day, -7, '2017-06-16')
AND RowDate <= DATEADD(day, -7, '2017-06-22')
THEN 1
ELSE 0
END), 0) AS preWeekOpenCnt
FROM dbo.RMS_CompanyList cc
LEFT JOIN CRM_Company c ON c.CompanyNo = cc.CompanyNo
LEFT JOIN CRM_CompanyStore cs ON cs.CompanyId = c.Id
WHERE c.IsDelete = 0
AND cs.IsDelete = 0
AND EXISTS ( SELECT *
FROM RMS_CompanyList e
WHERE e.CompanyId = cc.CompanyId
AND e.RowDate <= '2017-07-11'
AND e.CompanyName != '房屋管家'
AND e.CompanyName NOT LIKE '%实惠%'
AND e.CompanyName NOT LIKE '%测试%'
AND e.CityName IN ( '上海', '杭州', '郑州', '重庆', '武汉',
'合肥', '济南', '南京' ) )
GROUP BY CityName
--第三版
SELECT cc.CityName ,
SUM(t1.storeCnt) AS totalOpenCnt ,
SUM(CASE t2.flag
WHEN 1 THEN t1.storeCnt
ELSE 0
END) AS weekOpenCnt ,
SUM(CASE t3.flag
WHEN 1 THEN t1.storeCnt
ELSE 0
END) AS preWeekOpenCnt
FROM ( SELECT CityName ,
RowDate ,
CompanyNo ,
CASE WHEN CompanyName = '房屋管家' THEN 0
WHEN CHARINDEX('实惠', CompanyName) > 0 THEN 0
WHEN CHARINDEX('测试', CompanyName) > 0 THEN 0
ELSE 1
END AS CompanyStatus
FROM dbo.RMS_CompanyList
) cc
LEFT JOIN ( SELECT c.CompanyNo ,
COUNT(c.CompanyNo) storeCnt
FROM dbo.CRM_Company c
LEFT JOIN dbo.CRM_CompanyStore cs ON c.id = cs.CompanyId
WHERE cs.IsDelete = 0
AND c.IsDelete = 0
GROUP BY c.CompanyNo
) t1 ON t1.CompanyNo = cc.CompanyNo
LEFT JOIN ( SELECT 1 AS flag ,
companyNo
FROM dbo.RMS_CompanyList
WHERE RowDate >= '2017-06-16'
AND RowDate <= '2017-06-22'
) t2 ON t2.CompanyNo = cc.CompanyNo
LEFT JOIN ( SELECT 1 AS flag ,
companyNo
FROM dbo.RMS_CompanyList
WHERE RowDate >= DATEADD(day, -7, '2017-06-16')
AND RowDate <= DATEADD(day, -7, '2017-06-22')
) t3 ON t3.CompanyNo = cc.CompanyNo
WHERE RowDate <= '2017-07-11'
AND CityName IN ( '上海', '杭州', '郑州', '重庆', '武汉', '合肥', '济南', '南京' )
AND CompanyStatus = 1
GROUP BY CityName
--第四版
SELECT cc.CityName ,
ISNULL(SUM(1), 0) AS totalOpenCnt ,
ISNULL(SUM(CASE WHEN RowDate >= '2017-06-16'
AND RowDate <= '2017-06-22' THEN 1
ELSE 0
END), 0) AS weekOpenCnt ,
ISNULL(SUM(CASE WHEN RowDate >= DATEADD(day, -7, '2017-06-16')
AND RowDate <= DATEADD(day, -7, '2017-06-22')
THEN 1
ELSE 0
END), 0) AS preWeekOpenCnt
FROM ( SELECT CityName ,
RowDate ,
CompanyNo ,
CASE WHEN CompanyName = '房屋管家' THEN 0
WHEN CHARINDEX('实惠', CompanyName) > 0 THEN 0
WHEN CHARINDEX('测试', CompanyName) > 0 THEN 0
ELSE 1
END AS companyStatus
FROM dbo.RMS_CompanyList
) cc
LEFT JOIN CRM_Company c ON c.CompanyNo = cc.CompanyNo
LEFT JOIN CRM_CompanyStore cs ON cs.CompanyId = c.Id
WHERE c.IsDelete = 0
AND cs.IsDelete = 0
AND cc.companyStatus = 1
AND cc.RowDate <= '2017-07-11'
AND cc.CityName IN ( '上海', '杭州', '郑州', '重庆', '武汉', '合肥', '济南', '南京' )
GROUP BY CityName