ORACLE的一个时间范围的判断问题~

这两天做了一个很BT的东西,传过来的参数是B1_ALT_ID和一个循环的次数,那个循环的次数相当BT,是要找那个对应的时间点,我这里再画个图,大家清楚理解一下,它整个是一个workflow,是有很多项目的,一个INSPECTION做下来是有很多PROCESS的,它有一些的TASK的固定的STATUS是可以热循环的,如下图所示,一共热循环了四次,如果1.2.3都没问题,如1则可取1这个时间点及2这个时间点中间的符合条件的TASK即可。但是,如果是4呢,就只有大于4这个时间点,没有小于的时间点;再者如果这个workflow总共就热循环过一次,则它也是只有大于这个时间点,没有小于的时间点,所以我们中间去做了一次判断,如果是1或者是等于它的最大热循环次数的时候给它的小于的时间点给到一个不可能的最大值如“1/1/9999”

 

 

SQL如下,红色字体是关键~~我没有重新做例子,直接把我自己的SQL贴上来的,有思路即可,其它的不重要,继续加油~加涅个油~啊哦耶~~~~~

 其实下面很多冗余的东西,还没仔细整理,请大家忽略~~

WITH TMP1 AS

(select D.SERV_PROV_CODE,D.R3_ACT_TYPE_DES,D.R3_PROCESS_CODE,D.R3_ACT_STAT_COD,D.R3_ACT_STAT_DES

FROM sprocess C

INNER JOIN R3STATYP D

ON  C.SERV_PROV_CODE = D.SERV_PROV_CODE

AND C.R1_PROCESS_CODE = D.R3_PROCESS_CODE

AND C.SD_PRO_DES = D.R3_ACT_TYPE_DES

where C.r1_process_code=(SELECT B.R1_PROCESS_CODE FROM B1PERMIT A INNER JOIN R3APPTYP B

ON A.B1_PER_GROUP = B.R1_PER_GROUP

AND A.B1_PER_TYPE = B.R1_PER_TYPE

AND A.B1_PER_SUB_TYPE = B.R1_PER_SUB_TYPE

AND A.B1_PER_CATEGORY = B.R1_PER_CATEGORY

AND A.SERV_PROV_CODE = B.SERV_PROV_CODE

WHERE A. B1_ALT_ID = '2013-PROJ-000001'

AND A.SERV_PROV_CODE = 'DENVER')

AND C.SERV_PROV_CODE = 'DENVER'

AND D.R3_ACT_STAT_FLG = 'L'),

TMP2 as

(SELECT T.SD_PRO_DES,

      T.GA_LNAME,

      T.GA_MNAME,

      T.GA_FNAME,

       T.SD_APP_DES SUL12,

       T.G6_STAT_DD DATA13,

       T.SD_COMMENT COM14,

       A.SERV_PROV_CODE,

       T.R1_PROCESS_CODE

FROM GPROCESS T, B1PERMIT A

 WHERE  T.SERV_PROV_CODE = A.SERV_PROV_CODE

   AND T.B1_PER_ID1 = A.B1_PER_ID1

   AND T.B1_PER_ID2 = A.B1_PER_ID2

   AND T.B1_PER_ID3 = A.B1_PER_ID3

   AND T.SERV_PROV_CODE = 'DENVER'

   AND A.B1_ALT_ID = '2013-PROJ-000001'

   AND T.REC_STATUS = 'A' AND T.SD_APP_DES IS NOT NULL

UNION

SELECT T.SD_PRO_DES T,

       T.G6_ISS_LNAME,

       T.G6_ISS_MNAME ,

       T.G6_ISS_FNAME ,

       T.SD_APP_DES SUL12,

       T.SD_APP_DD DATA13,

       T.SD_COMMENT COM14,

       A.SERV_PROV_CODE,

       T.R1_PROCESS_CODE

   FROM GPROCESS_HISTORY T, B1PERMIT A

   WHERE  T.SERV_PROV_CODE = A.SERV_PROV_CODE

   AND T.B1_PER_ID1 = A.B1_PER_ID1

   AND T.B1_PER_ID2 = A.B1_PER_ID2

   AND T.B1_PER_ID3 = A.B1_PER_ID3

   AND T.SERV_PROV_CODE = 'DENVER'

   AND A.B1_ALT_ID = '2013-PROJ-000001'

   AND T.REC_STATUS = 'A' AND T.SD_APP_DES IS NOT NULL),

TMP3 AS(

SELECT TMP2.* ,ROW_NUMBER() OVER(ORDER BY DATA13) RNUM

FROM TMP2, TMP1

WHERE TMP1.SERV_PROV_CODE = TMP2.SERV_PROV_CODE

AND TMP1.R3_PROCESS_CODE = TMP2.R1_PROCESS_CODE

AND TMP1.R3_ACT_TYPE_DES = TMP2.SD_PRO_DES

AND TMP1.R3_ACT_STAT_DES = TMP2.SUL12

),

/*SELECT

   CASE

     WHEN (((SELECT MAX(RNUM) FROM TMP3)=1 AND 2=1) OR (SELECT MAX(RNUM) FROM TMP3) = 2) THEN

        TO_DATE('1/1/9999','mm/dd/yyyy')

     ELSE

        (SELECT DATA13 FROM TMP3 WHERE RNUM = 2+1)

     END ROW1,

   CASE

     WHEN (((SELECT MAX(RNUM) FROM TMP3)=1 AND 2=1) OR (SELECT MAX(RNUM) FROM TMP3) = 2) THEN

        (SELECT MAX(DATA13) FROM TMP3)

     ELSE

        (SELECT DATA13 FROM TMP3 WHERE RNUM = 2)

     END  ROW2

FROM TMP3 WHERE 2<=

(SELECT MAX(RNUM) FROM TMP3) AND 2>0

AND ROWNUM < 2*/

TMP4 AS (SELECT

   CASE

     WHEN (((SELECT MAX(RNUM) FROM TMP3)=1 AND 2=1) OR (SELECT MAX(RNUM) FROM TMP3) = 2) THEN

        TO_DATE('1/1/9999','mm/dd/yyyy')

     ELSE

        (SELECT DATA13 FROM TMP3 WHERE RNUM = 2+1)

     END ROW1,

   CASE

     WHEN (((SELECT MAX(RNUM) FROM TMP3)=1 AND 2=1) OR (SELECT MAX(RNUM) FROM TMP3) = 2) THEN

        (SELECT MAX(DATA13) FROM TMP3)

     ELSE

        (SELECT DATA13 FROM TMP3 WHERE RNUM = 2)

     END  ROW2

FROM TMP3 WHERE 2<=

(SELECT MAX(RNUM) FROM TMP3) AND 2>0

AND ROWNUM < 2)

SELECT T.SD_PRO_DES || REPLACE(REPLACE('(' || GA_LNAME || ',' || GA_MNAME || ' ' ||

                                       GA_FNAME || ')',

                                       ' ',

                                       ''),

                               ',',

                               ', ') VIWP11,

       T.SD_APP_DES SUL12,

       T.G6_STAT_DD DATA13,

       T.SD_COMMENT COM14

  FROM GPROCESS T, B1PERMIT A

 WHERE (T.SD_PRO_DES LIKE '%Review%' or T.SD_PRO_DES LIKE '%Referral%')

   AND T.SD_PRO_DES NOT LIKE '%Review%/%Finalize%'

   AND T.SERV_PROV_CODE = A.SERV_PROV_CODE

   AND T.B1_PER_ID1 = A.B1_PER_ID1

   AND T.B1_PER_ID2 = A.B1_PER_ID2

   AND T.B1_PER_ID3 = A.B1_PER_ID3

   AND T.SERV_PROV_CODE = 'DENVER'

   AND A.B1_ALT_ID = UPPER('2013-PROJ-000001')

   AND (A.B1_PER_GROUP || '/' || A.B1_PER_TYPE || '/' || A.B1_PER_SUB_TYPE || '/' ||

       A.B1_PER_CATEGORY IN

       ('Development/Project/NA/NA',

         'Development/Project/Concept Review/NA',

         'Development/Project/Site Development Plan Review/NA',

         'Development/Project/Trans Engr Plan Review/NA',

         'ROW/ROW Land Use/Relinquishment/NA',

         'ROW/ROW Land Use/Vacation/NA',

         'ROW/ROW Land Use/Utility Project/NA',

         'Development/Project/Site Development Plan Review/Subdivision',

         'ROW/Project/Capital Improvement Project/NA',

         'ROW/ROW Land Use/Major Encumbrance/NA',

         'Development/Project/Storm and Sanitary Plan Review/NA') OR

       A.B1_PER_GROUP || '/' || A.B1_PER_TYPE || '/' || A.B1_PER_SUB_TYPE =

       'Development/Building/Log')

   AND T.REC_STATUS = 'A'

   AND T.SD_APP_DES IS NOT NULL

   AND T.G6_STAT_DD >= (SELECT ROW2 FROM TMP4) AND T.G6_STAT_DD < (SELECT ROW1 FROM TMP4)

UNION

SELECT T.SD_PRO_DES || REPLACE(REPLACE('(' || G6_ISS_LNAME || ',' ||

                                       G6_ISS_FNAME || ' ' || G6_ISS_MNAME || ')',

                                       ' ',

                                       ''),

                               ',',

                               ', ') VIWP11,

       T.SD_APP_DES SUL12,

       T.SD_APP_DD DATA13,

       T.SD_COMMENT COM14

  FROM GPROCESS_HISTORY T, B1PERMIT A

 WHERE (T.SD_PRO_DES LIKE '%Review%' or T.SD_PRO_DES LIKE '%Referral%')

   AND T.SD_PRO_DES NOT LIKE '%Review%/%Finalize%'

   AND T.SERV_PROV_CODE = A.SERV_PROV_CODE

   AND T.B1_PER_ID1 = A.B1_PER_ID1

   AND T.B1_PER_ID2 = A.B1_PER_ID2

   AND T.B1_PER_ID3 = A.B1_PER_ID3

   AND T.SERV_PROV_CODE = 'DENVER'

   AND A.B1_ALT_ID = UPPER('2013-PROJ-000001')

   AND (A.B1_PER_GROUP || '/' || A.B1_PER_TYPE || '/' || A.B1_PER_SUB_TYPE || '/' ||

       A.B1_PER_CATEGORY IN

       ('Development/Project/NA/NA',

         'Development/Project/Concept Review/NA',

         'Development/Project/Site Development Plan Review/NA',

         'Development/Project/Trans Engr Plan Review/NA',

         'ROW/ROW Land Use/Relinquishment/NA',

         'ROW/ROW Land Use/Vacation/NA',

         'ROW/ROW Land Use/Utility Project/NA',

         'Development/Project/Site Development Plan Review/Subdivision',

         'ROW/Project/Capital Improvement Project/NA',

         'ROW/ROW Land Use/Major Encumbrance/NA',

         'Development/Project/Storm and Sanitary Plan Review/NA') OR

       A.B1_PER_GROUP || '/' || A.B1_PER_TYPE || '/' || A.B1_PER_SUB_TYPE =

       'Development/Building/Log')

   AND T.REC_STATUS = 'A'

   AND SD_APP_DES IS NOT NULL

   AND T.SD_APP_DD >= (SELECT ROW2 FROM TMP4) AND T.SD_APP_DD < (SELECT ROW1 FROM TMP4)

 ORDER BY DATA13 DESC

 

 

 

 

 

 

 

posted @ 2013-01-08 16:22 medci(卡樂江) 阅读(...) 评论(...) 编辑 收藏