PROC集计算

来源:互联网 发布:python isupper函数 编辑:程序博客网 时间:2024/07/17 21:23
--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

0 0
原创粉丝点击