SQL Join Over Non-Unique Data - Windowing/Over/Order By/Distinct

I've lurked long enough to know you folks will nail this, please pardon noobness:

Given some sample data:

Table JOBS

Table JOBSTEPS

select
a.JOBNAME,
a.JOBCOUNT,
a.STEPTOTAL,
b.STEPCOUNT,
b.PROGNAME,
b.EXTCMD,
b.SDLDATE,
b.SDLTIME,
a.SDLUNAME,
a.STRTDATE,
a.STRTTIME
from JOBS a inner join JOBSTEPS b on a.JOBNAME = b.JOBNAME and a.JOBCOUNT = b.JOBCOUNT where b.SDLDATE = a.STRTDATE

Need better join to perhaps involve a.STRTDATE, b.SDLDATE, a.STEPTOTAL, b.STEPCOUNT

• b.SDLDATE can be greater if steps run long
• a.STEPTOTAL is the highest b.STEPCOUNT
• without joining to later JOBNAME, JOBCOUNT data which is non-unique over time

I have tried joins like above by pushing SDLDATE out: however there are cases when JOBNAME, JOBCOUNT can be re-used again the next day - and other cases when SDLDATE for a given set of JOBSTEPS can span several days. So I either lose data or join too much to earlier JOBS records.

Provided you do not have overlapping sequences of steps, from Oracle 12, you can use MATCH_RECOGNIZE for row-by-row pattern patching to find the steps within a sequence and set the start date for each set of steps (even if there are multiple on the same day or if they go over multiple days). Once you have found that then you can use it to join the two tables:

SELECT j.*,
       MAX(s.sdldate) OVER (PARTITION BY s.jobname, s.jobcount, s.match) AS enddate,
       s.stepcount,
       s.sdldate
FROM   jobsteps
       MATCH_RECOGNIZE(
         PARTITION BY jobname, jobcount
         ORDER BY sdldate
         MEASURES
           step_one.sdldate AS strtdate,
           match_number()   AS match
         ALL ROWS PER MATCH
         PATTERN ( step_one next_step* )
         DEFINE
           step_one AS stepcount = 1,
           next_step AS stepcount = PREV(stepcount) + 1
       ) s
       INNER JOIN jobs j
       ON (   j.jobname   = s.jobname
          AND j.jobcount  = s.jobcount
          AND j.strtdate  = s.strtdate
          )
ORDER BY
       j.jobname, j.jobcount, s.sdldate

Which, for the sample data:

CREATE TABLE jobs (jobname, jobcount, steptotal, strtdate) AS
SELECT 'MIS_JOB_1', 23013400, 5, DATE '2024-04-29' + INTERVAL '23:01:42' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'MIS_JOB_1', 23013400, 5, DATE '2024-09-03' + INTERVAL '23:59:58' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'MIS_JOB_1', 23013400, 3, DATE '2024-09-04' + INTERVAL '01:00:00' HOUR TO SECOND FROM DUAL;

CREATE TABLE jobsteps (jobname, jobcount, stepcount, sdldate) AS
SELECT 'MIS_JOB_1', 23013400,  1,  DATE '2024-04-29' + INTERVAL '23:01:42' HOUR TO SECOND FROM DUAL UNION ALL 
SELECT 'MIS_JOB_1', 23013400,  2,  DATE '2024-04-29' + INTERVAL '23:01:43' HOUR TO SECOND FROM DUAL UNION ALL 
SELECT 'MIS_JOB_1', 23013400,  3,  DATE '2024-04-29' + INTERVAL '23:01:43' HOUR TO SECOND FROM DUAL UNION ALL 
SELECT 'MIS_JOB_1', 23013400,  4,  DATE '2024-04-29' + INTERVAL '23:01:44' HOUR TO SECOND FROM DUAL UNION ALL 
SELECT 'MIS_JOB_1', 23013400,  5,  DATE '2024-04-29' + INTERVAL '23:01:45' HOUR TO SECOND FROM DUAL UNION ALL 
SELECT 'MIS_JOB_1', 23013400,  1,  DATE '2024-09-03' + INTERVAL '23:59:58' HOUR TO SECOND FROM DUAL UNION ALL 
SELECT 'MIS_JOB_1', 23013400,  2,  DATE '2024-09-03' + INTERVAL '23:59:59' HOUR TO SECOND FROM DUAL UNION ALL 
SELECT 'MIS_JOB_1', 23013400,  3,  DATE '2024-09-03' + INTERVAL '23:59:59' HOUR TO SECOND FROM DUAL UNION ALL 
SELECT 'MIS_JOB_1', 23013400,  4,  DATE '2024-09-04' + INTERVAL '00:00:00' HOUR TO SECOND FROM DUAL UNION ALL 
SELECT 'MIS_JOB_1', 23013400,  5,  DATE '2024-09-04' + INTERVAL '00:00:01' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'MIS_JOB_1', 23013400,  1,  DATE '2024-09-04' + INTERVAL '01:00:00' HOUR TO SECOND FROM DUAL UNION ALL 
SELECT 'MIS_JOB_1', 23013400,  2,  DATE '2024-09-04' + INTERVAL '01:00:01' HOUR TO SECOND FROM DUAL UNION ALL 
SELECT 'MIS_JOB_1', 23013400,  3,  DATE '2024-09-04' + INTERVAL '01:00:02' HOUR TO SECOND FROM DUAL

Note: A DATE data-type has both date and time components. Don't store dates and times in separate columns (and especially not as strings), instead use a single DATE column containing both components. If you must store the components as two columns (please don't) then use TO_DATE(date_column || time_column, 'yyyymmddhh24miss') to convert them to a date and then use the query above).

Outputs:

fiddle