PROC集计算

--proc_xnh_mzbzjdbb '2014','*'

CREATE PROC proc_xnh_mzbzjdbb
    (
      @nf VARCHAR(4) ,
      @yf VARCHAR(2)
    )
AS 
    IF NULLIF('*', @yf) IS NOT NULL 
        BEGIN
-- 1  直接可用字段 次均门诊费用（jmzfy），次均门诊可报费用（jmzkbfy），
--门诊补助金额占门诊费用比例（bzzjebl），次均补助金额（jbzje），
--处理字段 补助金额 （补助金额占旗县区门诊总补助金额比例）
-- group by B.XZQBH 行政区,A.JGJB 机构级别 均门诊费用 均门诊可报费用 补助金额占总金额比例 均补助金额 补助金额
            SELECT  ISNULL(A.JGJB, '0') AS JGJB ,
                    B.XZQBH ,
                    SUM(MZZJE) / COUNT(XZQBH) AS JMZFY ,
                    SUM(KBCJE) / COUNT(XZQBH) AS JMZKBFY ,
                    SUM(SJBCJE) / SUM(MZZJE) BZZJEBL ,
                    SUM(SJBCJE) / COUNT(XZQBH) AS JBZJE ,
                    SUM(SJBCJE) AS BZJE
            FROM    ( SELECT    A.ZYJGBH ,
                                A.YLGMC ,
                                A.XZQBH
                      FROM      SKJBXX_XZQ B
                                LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH,
                                                              1, 4) = b.GJBH )
                      WHERE     B.GJBH LIKE '1508__'
                                OR B.GJBH = '1508'
                    ) AS B
                    LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH
            WHERE   DATEPART(year, MZRQ) = @nf
                    AND DATEPART(MONTH, MZRQ) = @yf
            GROUP BY B.XZQBH ,
                    A.JGJB
            ORDER BY B.XZQBH ASC
			
--  2 行政区划分 总的补助金额	（与上结果集1处理）
            SELECT  B.XZQBH ,
                    SUM(SJBCJE) AS ZSJBZJE
            FROM    ( SELECT    A.ZYJGBH ,
                                A.YLGMC ,
                                A.XZQBH
                      FROM      SKJBXX_XZQ B
                                LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH,
                                                              1, 4) = b.GJBH )
                      WHERE     B.GJBH LIKE '1508__'
                                OR B.GJBH = '1508'
                    ) AS B
                    LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH
            WHERE   DATEPART(year, MZRQ) = @nf
                    AND DATEPART(MONTH, MZRQ) = @yf
                    AND A.SJBCJE IS NOT NULL
                    AND a.SJBCJE > 0
            GROUP BY B.XZQBH
            ORDER BY B.XZQBH ASC
			
--  3 行政区机构级别划分 补助人数	
            SELECT  a.JGJB ,
                    B.XZQBH ,
                    COUNT(DISTINCT RYBH) AS RS
            FROM    ( SELECT    A.ZYJGBH ,
                                A.YLGMC ,
                                A.XZQBH
                      FROM      SKJBXX_XZQ B
                                LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH,
                                                              1, 4) = b.GJBH )
                      WHERE     B.GJBH LIKE '1508__'
                                OR B.GJBH = '1508'
                    ) AS B
                    LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH
            WHERE   DATEPART(year, MZRQ) = @nf
                    AND DATEPART(MONTH, MZRQ) = @yf
                    AND A.SJBCJE IS NOT NULL
                    AND a.SJBCJE > 0
            GROUP BY B.XZQBH ,
                    a.JGJB
            ORDER BY B.XZQBH ASC
					
-- 4  行政区划分 补助人数	（结果集3 和 4进行运算 得出 补助人数占旗县区总补助人数）
            SELECT  B.XZQBH ,
                    COUNT(DISTINCT RYBH) AS RS
            FROM    ( SELECT    A.ZYJGBH ,
                                A.YLGMC ,
                                A.XZQBH
                      FROM      SKJBXX_XZQ B
                                LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH,
                                                              1, 4) = b.GJBH )
                      WHERE     B.GJBH LIKE '1508__'
                                OR B.GJBH = '1508'
                    ) AS B
                    LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH
            WHERE   DATEPART(year, MZRQ) = @nf
                    AND DATEPART(MONTH, MZRQ) = @yf
                    AND A.SJBCJE IS NOT NULL
                    AND a.SJBCJE > 0
            GROUP BY B.XZQBH
            ORDER BY B.XZQBH ASC
			
			
--  5 行政区  --合计数据
--直接可用字段 次均门诊费用（jmzfy），次均门诊可报费用（jmzkbfy），
--门诊补助金额占门诊费用比例（bzzjebl），次均补助金额（jbzje），
            SELECT  B.XZQBH ,
                    SUM(MZZJE) / COUNT(XZQBH) AS JMZFY ,
                    SUM(KBCJE) / COUNT(XZQBH) AS JMZKBFY ,
                    SUM(SJBCJE) / SUM(MZZJE) BZZJEBL ,
                    SUM(SJBCJE) / COUNT(XZQBH) AS JBZJE
            FROM    ( SELECT    A.ZYJGBH ,
                                A.YLGMC ,
                                A.XZQBH
                      FROM      SKJBXX_XZQ B
                                LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH,
                                                              1, 4) = b.GJBH )
                      WHERE     B.GJBH LIKE '1508__'
                                OR B.GJBH = '1508'
                    ) AS B
                    LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH
            WHERE   DATEPART(year, MZRQ) = @nf
                    AND DATEPART(MONTH, MZRQ) = @yf
            GROUP BY B.XZQBH
            ORDER BY B.XZQBH ASC
			
-- 6 根据gjbh(国家编号)来group by 得出不同行政区的参合总人数
--集4/集6 旗县区补助人数占参合人数比例
            SELECT  XZQBH ,
                    COUNT(XZQBH) CHRS
            FROM    dbo.NHXX_CHCYXX a
                    LEFT JOIN dbo.NHXX_CHJTXX b ON a.JTBH = b.JTBH
            GROUP BY XZQBH

--	7 国家编号 与 行政区名称对照字典		
            SELECT  GJBH ,
                    XZQMC
            FROM    SKJBXX_XZQ
            WHERE   GJBH LIKE '1508__'
                    OR GJBH = '1508'
            ORDER BY GJBH ASC
        END
--月份为‘*’查询累计数据
    ELSE 
        BEGIN 
-- 1  直接可用字段 次均门诊费用（jmzfy），次均门诊可报费用（jmzkbfy），
--门诊补助金额占门诊费用比例（bzzjebl），次均补助金额（jbzje），
--处理字段 补助金额 （补助金额占旗县区门诊总补助金额比例）
-- group by B.XZQBH 行政区,A.JGJB 机构级别 均门诊费用 均门诊可报费用 补助金额占总金额比例 均补助金额 补助金额
            SELECT  ISNULL(A.JGJB, '0') AS JGJB ,
                    B.XZQBH ,
                    SUM(MZZJE) / COUNT(XZQBH) AS JMZFY ,
                    SUM(KBCJE) / COUNT(XZQBH) AS JMZKBFY ,
                    SUM(SJBCJE) / SUM(MZZJE) BZZJEBL ,
                    SUM(SJBCJE) / COUNT(XZQBH) AS JBZJE ,
                    SUM(SJBCJE) AS BZJE
            FROM    ( SELECT    A.ZYJGBH ,
                                A.YLGMC ,
                                A.XZQBH
                      FROM      SKJBXX_XZQ B
                                LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH,
                                                              1, 4) = b.GJBH )
                      WHERE     B.GJBH LIKE '1508__'
                                OR B.GJBH = '1508'
                    ) AS B
                    LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH
            WHERE   DATEPART(year, MZRQ) = @nf
            GROUP BY B.XZQBH ,
                    A.JGJB
            ORDER BY B.XZQBH ASC
			
--  2 行政区划分 总的补助金额	（与上结果集1处理）
            SELECT  B.XZQBH ,
                    SUM(SJBCJE) AS ZSJBZJE
            FROM    ( SELECT    A.ZYJGBH ,
                                A.YLGMC ,
                                A.XZQBH
                      FROM      SKJBXX_XZQ B
                                LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH,
                                                              1, 4) = b.GJBH )
                      WHERE     B.GJBH LIKE '1508__'
                                OR B.GJBH = '1508'
                    ) AS B
                    LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH
            WHERE   DATEPART(year, MZRQ) = @nf
                    AND A.SJBCJE IS NOT NULL
                    AND a.SJBCJE > 0
            GROUP BY B.XZQBH
            ORDER BY B.XZQBH ASC
			
--  3 行政区机构级别划分 补助人数	
            SELECT  a.JGJB ,
                    B.XZQBH ,
                    COUNT(DISTINCT RYBH) AS RS
            FROM    ( SELECT    A.ZYJGBH ,
                                A.YLGMC ,
                                A.XZQBH
                      FROM      SKJBXX_XZQ B
                                LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH,
                                                              1, 4) = b.GJBH )
                      WHERE     B.GJBH LIKE '1508__'
                                OR B.GJBH = '1508'
                    ) AS B
                    LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH
            WHERE   DATEPART(year, MZRQ) = @nf
                    AND A.SJBCJE IS NOT NULL
                    AND a.SJBCJE > 0
            GROUP BY B.XZQBH ,
                    a.JGJB
            ORDER BY B.XZQBH ASC
					
-- 4  行政区划分 补助人数	（结果集3 和 4进行运算 得出 补助人数占旗县区总补助人数）
            SELECT  B.XZQBH ,
                    COUNT(DISTINCT RYBH) AS RS
            FROM    ( SELECT    A.ZYJGBH ,
                                A.YLGMC ,
                                A.XZQBH
                      FROM      SKJBXX_XZQ B
                                LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH,
                                                              1, 4) = b.GJBH )
                      WHERE     B.GJBH LIKE '1508__'
                                OR B.GJBH = '1508'
                    ) AS B
                    LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH
            WHERE   DATEPART(year, MZRQ) = @nf
                    AND A.SJBCJE IS NOT NULL
                    AND a.SJBCJE > 0
            GROUP BY B.XZQBH
            ORDER BY B.XZQBH ASC
			
			
--  5 行政区  --合计数据
--直接可用字段 次均门诊费用（jmzfy），次均门诊可报费用（jmzkbfy），
--门诊补助金额占门诊费用比例（bzzjebl），次均补助金额（jbzje），
            SELECT  B.XZQBH ,
                    SUM(MZZJE) / COUNT(XZQBH) AS JMZFY ,
                    SUM(KBCJE) / COUNT(XZQBH) AS JMZKBFY ,
                    SUM(SJBCJE) / SUM(MZZJE) BZZJEBL ,
                    SUM(SJBCJE) / COUNT(XZQBH) AS JBZJE
            FROM    ( SELECT    A.ZYJGBH ,
                                A.YLGMC ,
                                A.XZQBH
                      FROM      SKJBXX_XZQ B
                                LEFT JOIN NHXX_YLJG A ON ( SUBSTRING(a.XZQBH,
                                                              1, 4) = b.GJBH )
                      WHERE     B.GJBH LIKE '1508__'
                                OR B.GJBH = '1508'
                    ) AS B
                    LEFT JOIN NHXX_MZFYXX A ON A.MZJG = B.ZYJGBH
            WHERE   DATEPART(year, MZRQ) = @nf
            GROUP BY B.XZQBH
            ORDER BY B.XZQBH ASC
			
-- 6 根据gjbh(国家编号)来group by 得出不同行政区的参合总人数
--集4/集6 旗县区补助人数占参合人数比例
            SELECT  XZQBH ,
                    COUNT(XZQBH) CHRS
            FROM    dbo.NHXX_CHCYXX a
                    LEFT JOIN dbo.NHXX_CHJTXX b ON a.JTBH = b.JTBH
            GROUP BY XZQBH

--	7 国家编号 与 行政区名称对照字典		
            SELECT  GJBH ,
                    XZQMC
            FROM    SKJBXX_XZQ
            WHERE   GJBH LIKE '1508__'
                    OR GJBH = '1508'
            ORDER BY GJBH ASC
        END