I have a view where I am displaying NO_OF_DAYS_AGING based on MODIFIED_DATE but for some rows, there are 2 MODIFIED_DATE. So I want to calculate based on the latest MODIFIED_DATE.
Here is NO_OF_DAYS_AGING logic.
ROUND ((SYSDATE - JPD.MODIFIED_DATE), 0) as "JPD.MODIFIED_DATE",
Also please see the whole VIEW for reference.
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.job_id) 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 ORDER 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 ORDER 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 ORDER 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 ORDER 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,
-- (SYSDATE - JPD.MODIFIED_DATE) as "JPD.MODIFIED_DATE"
ROUND ((SYSDATE - JPD.MODIFIED_DATE), 0) as "JPD.MODIFIED_DATE",
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;Ignoring your huge, incomprehensible view (for which you have not provided any CREATE TABLE statements or any INSERT statements for sample data so we cannot comprehend or replicate the problem).
If we create a minimal representative example with 3 columns:
CREATE TABLE table_name (x, y, z) AS
SELECT 1, 2, TRUNC(SYSDATE - 1) FROM DUAL UNION ALL
SELECT 1, 4, TRUNC(SYSDATE - 7) FROM DUAL UNION ALL
SELECT 2, 5, SYSDATE - 3 FROM DUAL UNION ALL
SELECT 2, 6, TRUNC(SYSDATE - 3) FROM DUAL UNION ALL
SELECT 3, 1, TRUNC(SYSDATE) FROM DUAL;
And want to solve the problem:
Find the number of days (rounded to the nearest whole day) from today to the maximum
zvalue for eachxand return this for every row.
Then you can use the analytic MAX function:
SELECT x, y, z,
SYSDATE AS time_now,
ROUND(SYSDATE - z, 0) AS no_days_for_this_row,
ROUND(SYSDATE - MAX(z) OVER (PARTITION BY x), 0) AS no_days_for_x
FROM table_name;
Which outputs:
| X | Y | Z | TIME_NOW | NO_DAYS_FOR_THIS_ROW | NO_DAYS_FOR_X |
|---|---|---|---|---|---|
| 1 | 2 | 2023-10-11 00:00:00 | 2023-10-12 09:37:22 | 1 | 1 |
| 1 | 4 | 2023-10-05 00:00:00 | 2023-10-12 09:37:22 | 7 | 1 |
| 2 | 5 | 2023-10-09 09:37:22 | 2023-10-12 09:37:22 | 3 | 3 |
| 2 | 6 | 2023-10-09 00:00:00 | 2023-10-12 09:37:22 | 3 | 3 |
| 3 | 1 | 2023-10-12 00:00:00 | 2023-10-12 09:37:22 | 0 | 0 |
You should apply the same technique to your huge view.