I am interested in a start time of a set of packages that can be started or not started. I want the start time of the first started package. So the minimum start time, but not initial start time. To make things more complex, there are several sets of packages which are defined by a combination of run_id + step_id. So I have a pool of multiple runs each with multiple steps each with multiple packages.
lt_pkg_rtimes = SELECT *, -- some more fields
MIN("EXEC_STARTTIME") OVER
( PARTITION BY "RUN_ID","STEP_ID" ORDER BY "RUN_ID","STEP_ID" ) --has to be 2nd minimum
AS STEP_TSTMAP_START,
FROM :lt_pkgdata --contains all start times, including initial
ORDER BY "RUN_ID",
"STEP_ID";
So, I tried to replace lt_pkgdata with lt_pkg_min which I create like follows.
--finding second minimum execution start time
lt_pkg_started = SELECT * FROM :lt_pkgdata AS pkg WHERE pkg.exec_starttime > 0;
--Only if there is no 2nd minimum per run+step, also include the 1st minimum
lt_pkg_min = SELECT
client,
run_id,
step_id,
CASE
WHEN exec_starttime > 0
THEN ( SELECT MIN("EXEC_STARTTIME")
FROM :lt_pkg_started AS std
WHERE std.client = client -- this where does not work like I need it
AND std.run_id = run_id
AND std.step_id = step_id )
ELSE
exec_starttime
END AS exec_starttime,
FROM :lt_pkgdata;
The result of my try is that the second minimum of all the packages of all run_id + step_id combinations is used. But I need it per run_id + step_id combination.
Also, I am working in an AMDP (SAP HANA SQL).