Find last finish task using oracle sql-CodePudding

I need to find the last finish task using sql in each group. I have two conditions. construction_type = 0 and construction_type in (1, 2). For construction_type = 0. Only completed task will be fetch. But for construction_type in (1, 2). Completed and not completed task can be fetch. I made the query but the problem is if tasks are finish and some new task enter which is not completed then this non completed task is coming at top. I tried different order by but somehow unable to get the last completed task at top in case of construction_type in (1, 2). For construction_type = 0 there is no problem because only the completed tasks will be fetch.

I am ordering by on task_last_modified_date. If some new task will enter then it has highest task_last_modified_date. That's why my uncompleted task is coming on top. It should be something like latest task_last_modified_date with completed task. But I am not sure how do I do it.

Here is the sample data

CREATE TABLE project_complete_data (CONSTRUCTION_TYPE, PROJECT_ID, PROJ_COMPANY_NAME,   CUSTOM_LOT_NO, LOT_STATUS, SCHEDULE_TASK_ID, TASK_NAME, TASK_STATUS, TASK_ACTUAL_END_DATE, TASK_FINISH_DATE, TASK_FINISHED, TASK_LAST_MODIFIED_DATE, PO_TOTAL_AMOUNT, PO_STATUS, PO_TYPE) AS
          SELECT 1, 511, 'Amber Hills Inc.', '0000000077', 102, 10961162, 'electrical rough',                     101, DATE '2021-12-13', DATE '2021-11-19', 0, TO_DATE('2021-11-16 14:44:04', 'YYYY-MM-DD hh24:mi:ss'),    49.7, 100, 101  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', '0000000077', 102, 10961161, 'plumbing rough',                       101, DATE '2021-12-06', DATE '2021-11-12', 1, TO_DATE('2021-11-15 15:22:13', 'YYYY-MM-DD hh24:mi:ss'),    0.3,  101, 101  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', '0000000077', 102, 10961159, 'roof framing',                         101, DATE '2021-11-29', DATE '2021-11-15', 1, TO_DATE('2021-11-15 13:04:37', 'YYYY-MM-DD hh24:mi:ss'),    10,   101, 101  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', '0000000077', 102, 10961158, 'second floor framing',                 101, DATE '2021-11-15', DATE '2021-11-09', 1, TO_DATE('2021-11-09 15:22:42', 'YYYY-MM-DD hh24:mi:ss'),    50,   101, 101  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', '0000000077', 102, 10961157, 'first floor framing',                  101, DATE '2021-11-08', DATE '2021-11-09', 1, TO_DATE('2021-11-09 14:44:57', 'YYYY-MM-DD hh24:mi:ss'),    0,    101, 101  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', '0000000077', 102, 10961157, 'first floor framing',                  101, DATE '2021-11-08', DATE '2021-11-09', 1, TO_DATE('2021-11-09 14:44:57', 'YYYY-MM-DD hh24:mi:ss'),    0,    101, 101  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', '01a',        103, 10134563, 'Rough-In Plumbing',                    101, DATE '2009-12-17', DATE '2014-09-01', 1, NULL,                                                       0,    100, 100  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', '02',         103, 2213016,  'Update Lender Info in BT - Sales Rep', 101, DATE '2005-06-28', DATE '2005-10-11', 1, NULL,                                                       0,    100, 100  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', '020',        103, 10955239, 'TK task',                              101, DATE '2018-11-07', DATE '2019-06-21', 1, TO_DATE('2019-06-21 18:12:21', 'YYYY-MM-DD hh24:mi:ss'),    0,    100, 101  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', '020',        103, 10955239, 'TK task',                              101, DATE '2018-11-07', DATE '2019-06-21', 1, TO_DATE('2019-06-21 18:12:21', 'YYYY-MM-DD hh24:mi:ss'),    33,   100, 100  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', 'testpa01',   102, 10959716, 'HVAC rough',                           101, DATE '2021-09-17', DATE '2021-09-15', 0, TO_DATE('2021-11-02 12:28:57', 'YYYY-MM-DD hh24:mi:ss'),    0,    101, 100  FROM DUAL;

Here is the query

select 
    proj_company_name, 
    custom_lot_no,
    task_name,
    task_actual_end_date,                           
    task_finish_date,
    task_finished,
    task_last_modified_date,
    to_char(task_last_modified_date, 'YYYY-MM-DD hh24:mi:ss') AS TASK_LAST_MODIFIED_DATE_TIME,
    ROW_NUMBER() OVER (PARTITION BY custom_lot_no ORDER BY to_char(task_last_modified_date, 'YYYY-MM-DD hh24:mi:ss') desc) AS LAST_TASK_FINISH_SEQ,
    po_total_amount,
    po_status,
    CASE 
        WHEN po_status = 101 THEN po_total_amount   
        ELSE 0
    END as POs_COMPLETED,
    po_type,
    CASE 
        WHEN po_type = 100 THEN po_total_amount 
        ELSE 0
    END as "COMMITTED"
FROM project_complete_data
where project_id = 511
AND po_status in ('100', '101')
AND lot_status in ('102', '103')
And task_status = 101
AND (
        (construction_type = 0 AND task_finished = 1 and (schedule_task_id is not null or schedule_task_id > 0))    
        OR (construction_type in ('1', '2') AND task_finished in ('1', '0'))                
) 
order by custom_lot_no, to_char(task_last_modified_date, 'YYYY-MM-DD hh24:mi:ss') desc;

This query is giving the following result

As you can see the top record has highest last_modified_date_time and it is coming at last_task_finish_seq at 1. But this task is not completed becasue its task_finished is 0. So the first record should be the second one becasue although it last_modified_date_time is less but it is completed. And it is completed in last because it has higher last_modified_date_time then other completed tasks.

How can I do it? Actually after getting each group record on top I will use some thing like

`select * from (above query) where LAST_TASK_FINISH_SEQ = 1;`

Because then I know that I have the last completed task at top of each group.

Thanks

CodePudding user response：

To get the most recently completed row (or the latest row, if no rows are completed and construction_type IN (1,2)), you can use:

SELECT *
FROM   (
  SELECT *
  FROM   project_complete_data
  WHERE  project_id = 511
  AND    po_status IN (100, 101)
  AND    lot_status IN (102, 103)
  AND    task_status = 101
  AND    (  (   construction_type = 0
            AND task_finished = 1
            AND schedule_task_id IS NOT NULL)
         OR construction_type IN (1, 2)
         )
)
MATCH_RECOGNIZE (
  PARTITION BY custom_lot_no
  ORDER     BY task_finished DESC, task_last_modified_date DESC
  ALL ROWS PER MATCH
  PATTERN ( ^ latest_finished_row )
  DEFINE
    latest_finished_row AS 1 = 1
)

If you want to order the rows without filtering:

select proj_company_name, 
       custom_lot_no,
       task_name,
       task_actual_end_date,                           
       task_finish_date,
       task_finished,
       task_last_modified_date,
       to_char(task_last_modified_date, 'YYYY-MM-DD hh24:mi:ss')
         AS TASK_LAST_MODIFIED_DATE_TIME,
       ROW_NUMBER() OVER (
         PARTITION BY custom_lot_no
         ORDER BY task_finished DESC, task_last_modified_date desc
       ) AS LAST_TASK_FINISH_SEQ,
       po_total_amount,
       po_status,
       CASE 
       WHEN po_status = 101
       THEN po_total_amount   
       ELSE 0
       END as POs_COMPLETED,
       po_type,
       CASE 
       WHEN po_type = 100
       THEN po_total_amount 
       ELSE 0
       END as "COMMITTED"
FROM   project_complete_data
where  project_id = 511
AND    po_status  in (100, 101)
AND    lot_status in (102, 103)
And    task_status = 101
AND    (  (   construction_type = 0
          AND task_finished = 1 
          AND schedule_task_id is not null
          )    
       OR (   construction_type in (1, 2)
          AND task_finished in (1, 0)
          ) 
) 
order by custom_lot_no, task_last_modified_date desc;

db<>fiddle here