一次sql脚本改造

---报表：城市、截止开通数、上周开通数、上上周开通数


--第一版
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