Oracle中基于最新MODIFIED_DATE计算NO_OF_DAYS_AGING的方案

我创建了名为APP_FIBERINV.CMP_PANINDIA_VIEW_AGING_DAYS的视图，原本基于MODIFIED_DATE字段计算NO_OF_DAYS_AGING，但部分行存在多个MODIFIED_DATE值，现需基于最新的MODIFIED_DATE进行计算。

原NO_OF_DAYS_AGING计算逻辑：

ROUND ((SYSDATE - JPD.MODIFIED_DATE), 0) as "NO_OF_DAYS_AGING",

优化后完整视图创建代码如下：

CREATE OR REPLACE VIEW "APP_FIBERINV"."CMP_PANINDIA_VIEW_AGING_DAYS" ("CIRCLE", "MP", "SPAN_TYPE", "SPAN_LINK_ID", "NE_LENGTH", "ROUTE_ACCEPTED", "LIT_ACCEPTED", "MISSING_ASBUILT", "STATUS", "OFFERED_DATE", "HOTO_ACCEPTED_DATE", "LIT_ACCEPTED_DATE", "ASSIGNED_BY", "ASSIGNED_TO", "JOB_PROGRESS_FLAG", "ROUTE_APPROVED_BY_CMM", "LIT_APPROVED_BY_CMM", "CMM_APPROVED_DATE", "REOFFERFLAG", "NO_OF_DAYS_AGING", "REJECT_REMARKS") AS 
  WITH MAIN_DATA AS 
(
        SELECT DISTINCT MZB.JIOSTATENAME,MZB.JIOSTATECODE,MZB.MAINTENANCEZONENAME,MZB.MAINTENANCEZONECODE
        FROM R4G_LB.MANTAINENCEBOUNDARY_EVW MZB
          ORDER BY MZB.JIOSTATENAME
),
HOTOSTATUS AS 
(
    SELECT SM.STATUS_ID,SM.STATUS_NAME AS STATUS FROM APP_FIBERINV.TBL_FIBER_INV_STATUS_MASTER SM
),
JOBS_DATA AS 
(
      SELECT DISTINCT JB.JOB_ID,NVL(JB.SPAN_ID,'NA') AS SPAN_ID,
      NVL(JB.LINK_ID,'NA') AS LINK_ID, 
      NVL(JB.FSA_ID,'NA') AS FSA_ID,
      CASE WHEN JB.SPAN_TYPE = 'INTERCITY' THEN JB.SPAN_ID
            WHEN JB.SPAN_TYPE = 'INTRACITY' THEN JB.LINK_ID
            WHEN JB.SPAN_TYPE = 'ENTERPRISE' THEN JB.LINK_ID
            WHEN JB.SPAN_TYPE = 'FTTX' THEN JB.FSA_ID
      END AS CLUBED_SPAN,
      JB.MAINTENANCEZONECODE AS MAINTENANCE_CODE,
      JB.CREATED_DATE AS OFFERED_DATE,
      NVL(JB.MAINT_ZONE_NE_SPAN_LENGTH,0) AS NE_LENGTH,
      NVL(JB.MISSING_ABD_LENGTH,0) AS MISSING_ABD_lENGTH,
      JB.SPAN_TYPE,JB.JOB_FLAG,JB.REOFFERFLAG
      FROM APP_FIBERINV.TBL_FIBER_INV_JOBS JB where not exists(SELECT SPAN_LINK_ID FROM 
      APP_FIBERINV.TBL_FIBER_INV_DROPSPAN kb where jb.SPAN_ID=kb.SPAN_LINK_ID) or not exists (SELECT SPAN_LINK_ID
      FROM APP_FIBERINV.TBL_FIBER_INV_DROPSPAN kb where jb.SPAN_ID=kb.SPAN_LINK_ID)
),
JOBPROGRESS_DATA AS 
(
     SELECT T.JOB_ID,T.STATUS_ID,
     T.HOTO_ACTUAL_LENGTH,
     T.LIT_ACTUAL_LENGTH,
     T.HOTO_OFFERED_DATE,
     T.HOTO_ACCEPTED_DATE,T.LIT_ACCEPTED_DATE,     
     T.JOB_PROGRESS_FLAG,
     T.MODIFIED_DATE,
     T.APPROV_REJECT_REMARK
     FROM 
     (
        SELECT DISTINCT JBP.JOB_PROGRESS_ID,JBP.JOB_ID,ROW_NUMBER() OVER (PARTITION by JBP.job_id ORDER BY JBP.MODIFIED_DATE DESC) AS SRNO,
        JBP.STATUS_ID,        
        SUM(NVL((CASE WHEN JBP.REJECTED_BY LIKE ('%') AND JBP.UMS_GROUP_ASS_TO_NAME LIKE ('Construction_Engineer') AND JBP.JOB_PROGRESS_FLAG = 0 THEN 0 ELSE JBP.HOTO_ACTUAL_LENGTH END),0)) over (PARTITION by JBP.job_id) AS HOTO_ACTUAL_LENGTH,
        SUM(NVL((CASE WHEN JBP.REJECTED_BY LIKE ('%') AND JBP.UMS_GROUP_ASS_TO_NAME LIKE ('Construction_Engineer') AND JBP.JOB_PROGRESS_FLAG = 0 THEN 0 ELSE JBP.LIT_ACTUAL_LENGTH END),0)) over (PARTITION by JBP.job_id) AS LIT_ACTUAL_LENGTH,                
        HOTO_OFFERED_DATE,
        MAX(DECODE(JBP.HOTO_ACCEPTENCE_DATE,NULL,'01-JAN-01',JBP.HOTO_ACCEPTENCE_DATE)) over(PARTITION by JBP.job_id) AS HOTO_ACCEPTED_DATE,
        MAX(DECODE(JBP.LIT_ACCEPTENCE_DATE,NULL,'01-JAN-01',JBP.LIT_ACCEPTENCE_DATE)) over(PARTITION by JBP.job_id) AS LIT_ACCEPTED_DATE,                
        JBP.JOB_PROGRESS_FLAG, JBP.MODIFIED_DATE, JBP.APPROV_REJECT_REMARK
         FROM  APP_FIBERINV.TBL_FIBER_INV_JOB_PROGRESS JBP           
      )T WHERE T.SRNO = 1
),
ASSIGNDATA AS
(
SELECT JOB_ID,ASSIGNED_BY,ASSIGNED_TO FROM (
   SELECT DISTINCT X.JOB_ID, DECODE(Y.UMS_GROUP_ASS_BY_NAME,NULL,'NA',Y.UMS_GROUP_ASS_BY_NAME) AS ASSIGNED_BY,
   DECODE(Y.UMS_GROUP_ASS_TO_NAME,NULL,'NA',Y.UMS_GROUP_ASS_TO_NAME) AS ASSIGNED_TO
    FROM 
   (
      SELECT Y1.JOB_ID,Y1.UMS_GROUP_ASS_TO_DATE,Y1.job_progress_flag FROM (
         SELECT JP1.JOB_ID,JP1.job_progress_flag,row_number() over (partition by JP1.JOB_ID order by JP1.JOB_ID) SRNO,
        MAX(DECODE (JP1.UMS_GROUP_ASS_TO_DATE,NULL,TO_DATE('01-01-1801','DD-MM-YYYY'),JP1.UMS_GROUP_ASS_TO_DATE)) AS UMS_GROUP_ASS_TO_DATE
        FROM APP_FIBERINV.TBL_FIBER_INV_JOB_PROGRESS JP1 
        GROUP BY JP1.JOB_ID,JP1.job_progress_flag
        order by  JP1.JOB_ID
        )Y1 WHERE Y1.SRNO = 1
    )X,APP_FIBERINV.tbl_fiber_inv_job_progress Y
    WHERE X.JOB_ID = Y.job_id 
    AND (CASE WHEN X.UMS_GROUP_ASS_TO_DATE = TO_DATE('01-01-1801','DD-MM-YYYY') 
     THEN 1 ELSE CASE WHEN TO_DATE(X.UMS_GROUP_ASS_TO_DATE,'DD-MM-YYYY') = TO_DATE(Y.UMS_GROUP_ASS_TO_DATE,'DD-MM-YYYY')  THEN 1 ELSE 0 END END) = 1
     AND X.job_progress_flag = Y.job_progress_flag
    )ORDER BY JOB_ID    
),
CMMAPPROVEDKM AS 
(
  SELECT JOB_ID,MAINTENANCE_CODE,ROUTE_APPROVED_BY_CMM, LIT_APPROVED_BY_CMM,CMM_APPROVED_DATE FROM tbl_fiber_inv_cmpapproved_info
)
SELECT DISTINCT MD.JIOSTATENAME AS CIRCLE,MD.MAINTENANCEZONENAME||'/'||JD.MAINTENANCE_CODE AS MP,JD.SPAN_TYPE AS SPAN_TYPE,
JD.CLUBED_SPAN AS SPAN_LINK_ID,
JD.NE_LENGTH,
JPD.HOTO_ACTUAL_LENGTH AS ROUTE_ACCEPTED,
JPD.LIT_ACTUAL_LENGTH AS LIT_ACCEPTED,
JD.MISSING_ABD_lENGTH AS MISSING_ASBUILT,
HS.STATUS,
JD.OFFERED_DATE,
DECODE(JPD.HOTO_ACCEPTED_DATE,'01-JAN-01','NOT PRESENT',JPD.HOTO_ACCEPTED_DATE) AS HOTO_ACCEPTED_DATE,
DECODE(JPD.LIT_ACCEPTED_DATE,'01-JAN-01','NOT PRESENT',JPD.LIT_ACCEPTED_DATE) AS LIT_ACCEPTED_DATE,
AD.ASSIGNED_BY,
AD.ASSIGNED_TO,
JPD.JOB_PROGRESS_FLAG,
CMK.ROUTE_APPROVED_BY_CMM AS ROUTE_APPROVED_BY_CMM,CMK.LIT_APPROVED_BY_CMM AS LIT_APPROVED_BY_CMM,
CMK.CMM_APPROVED_DATE,
JD.REOFFERFLAG,
ROUND ((SYSDATE - JPD.MODIFIED_DATE), 0) as "NO_OF_DAYS_AGING",
JPD.APPROV_REJECT_REMARK
FROM JOBS_DATA JD
LEFT JOIN JOBPROGRESS_DATA JPD ON JPD.JOB_ID = JD.JOB_ID
LEFT JOIN ASSIGNDATA AD ON AD.JOB_ID = JD.JOB_ID AND AD.JOB_ID = JPD.JOB_ID
LEFT JOIN CMMAPPROVEDKM CMK ON CMK.JOB_ID = JD.JOB_ID AND CMK.JOB_ID = AD.JOB_ID AND CMK.JOB_ID = JPD.JOB_ID AND JD.MAINTENANCE_CODE=CMK.MAINTENANCE_CODE
LEFT JOIN MAIN_DATA MD ON MD.MAINTENANCEZONECODE = JD.MAINTENANCE_CODE
LEFT JOIN HOTOSTATUS HS ON HS.STATUS_ID = JPD.STATUS_ID
WHERE JD.NE_LENGTH > 0
ORDER BY SPAN_TYPE,CIRCLE,MP;

内容的提问来源于stack exchange，提问作者Nadeem