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
