续租分析情况表

     SELECT gmrq,
            SUM (zysl) AS zysl,
            SUM (tmsl) AS tmsl,
            SUM (qcsl) AS qcsl,
            SUM (qfsl) AS qfsl,
            SUM (xzsl) AS xzsl,
            SUM (jgsl) AS jgsl,
            SUM (wxzsl) AS wxzsl
       FROM (                                                           -- 租用数
             SELECT   TO_CHAR (rq, 'yyyy') AS gmrq,
                      COUNT (*) AS zysl,
                      0 AS tmsl,
                      0 AS qcsl,
                      0 AS qfsl,
                      0 AS xzsl,
                      0 AS jgsl,
                      0 AS wxzsl
                 FROM ywdjb
                WHERE zxflag <> '1' AND azfsdm = '01'
             GROUP BY TO_CHAR (rq, 'yyyy')
             UNION ALL
               --退墓数
               SELECT TO_CHAR (rq, 'yyyy') AS gmrq,
                      0 AS zysl,
                      COUNT (*) AS tmsl,
                      0 AS qcsl,
                      0 AS qfsl,
                      0 AS xzsl,
                      0 AS jgsl,
                      0 AS wxzsl
                 FROM ywdjb
                WHERE zxflag <> '1' AND azfsdm = '01' AND zxflag = '3'
             GROUP BY TO_CHAR (rq, 'yyyy')
             UNION ALL
               --迁出数
               SELECT TO_CHAR (rq, 'yyyy') AS gmrq,
                      0 AS zysl,
                      0 AS tmsl,
                      COUNT (*) AS qcsl,
                      0 AS qfsl,
                      0 AS xzsl,
                      0 AS jgsl,
                      0 AS wxzsl
                 FROM ywdjb
                WHERE zxflag <> '1' AND azfsdm = '01' AND zxflag = '2'
             GROUP BY TO_CHAR (rq, 'yyyy')
             UNION ALL
               --迁坟数量
               SELECT TO_CHAR (rq, 'yyyy') AS gmrq,
                      0 AS zysl,
                      0 AS tmsl,
                      0 AS qcsl,
                      COUNT (*) AS qfsl,
                      0 AS xzsl,
                      0 AS jgsl,
                      0 AS wxzsl
                 FROM ywdjb
                WHERE zxflag <> '1' AND azfsdm = '01' AND qfflag = '1'
             GROUP BY TO_CHAR (rq, 'yyyy')
             UNION ALL
               --续租数量
               SELECT TO_CHAR (t2.rq, 'yyyy') AS gmrq,
                      0 AS zysl,
                      0 AS tmsl,
                      0 AS qcsl,
                      0 AS qfsl,
                      COUNT (t1.djh) AS xzsl,
                      0 AS jgsl,
                      0 AS wxzsl
                 FROM (SELECT djh,
                              ywdjh,
                              azfsdm,
                              ywmc,
                              zxflag
                         FROM qtywb t3
                        WHERE zxflag <> '1'
                              AND (ywmc = '续租' OR ywmc LIKE '%改%')
                              AND rq =
                                     (SELECT MAX (rq)
                                        FROM qtywb
                                       WHERE t3.djh = djh AND zxflag <> '1'
                                             AND (ywmc = '续租'
                                                  OR ywmc LIKE '%改%'))) t1,
                      ywdjb t2
                WHERE     t1.djh = t2.djh
                      AND t1.zxflag <> '1'
                      AND t1.azfsdm = '01'
                      AND t2.zxflag <> '1'
                      AND t1.ywmc = '续租'
             GROUP BY TO_CHAR (t2.rq, 'yyyy')
             UNION ALL
               --旧改数量
               SELECT TO_CHAR (t2.rq, 'yyyy') AS gmrq,
                      0 AS zysl,
                      0 AS tmsl,
                      0 AS qcsl,
                      0 AS qfsl,
                      0 AS xzsl,
                      COUNT (t1.djh) AS jgsl,
                      0 AS wxzsl
                 FROM (SELECT djh,
                              ywdjh,
                              azfsdm,
                              ywmc,
                              zxflag
                         FROM qtywb t3
                        WHERE zxflag <> '1'
                              AND (ywmc = '续租' OR ywmc LIKE '%改%')
                              AND rq =
                                     (SELECT MAX (rq)
                                        FROM qtywb
                                       WHERE t3.djh = djh AND zxflag <> '1'
                                             AND (ywmc = '续租'
                                                  OR ywmc LIKE '%改%'))) t1,
                      ywdjb t2
                WHERE     t1.djh = t2.djh
                      AND t1.zxflag <> '1'
                      AND t1.azfsdm = '01'
                      AND t2.zxflag <> '1'
                      AND t1.ywmc LIKE '%改%'
             GROUP BY TO_CHAR (t2.rq, 'yyyy')
             UNION ALL
               --已到期未续租名单
               SELECT TO_CHAR (t1.rq, 'yyyy') AS gmrq,
                      0 AS zysl,
                      0 AS tmsl,
                      0 AS qcsl,
                      0 AS qfsl,
                      0 AS xzsl,
                      0 AS jgsl,
                      COUNT (*) AS wxzsl
                 FROM ywdjb t1
                WHERE     t1.zxflag = '0'
                      AND t1.azfsdm = '01'
                      AND EXISTS
                             (SELECT *
                                FROM V_BS_CURRENTPERIOD
                               WHERE SYSDATE > jsrq AND t1.djh = djh)
             GROUP BY TO_CHAR (t1.rq, 'yyyy'))
   GROUP BY gmrq
   ORDER BY gmrq;
COMMENT ON TABLE  V_NDXSBB IS '年度销售报表';