sqlsql-serverhp-alm

How to get record based on Exec Date and Exec Time column?

I would like to get the latest record based on Exec Date and Exec Time columns.

Type of Exec Date column is datetime. Type of Exec Time column is varchar.

However, as it is not on the same column, I'm not able to do the filter.

Is it possible to do it with SQL?

Example

Data

RN_TEST_ID  | RN_RUN_ID  | RN_EXECUTION_DATE   | RN_EXECUTION_TIME | ST_STATUS
585858      | 214566     | 2022-04-04 00:00:00 |  16:30:12         | P
585858      | 214566     | 2022-04-04 00:00:00 |  16:30:12         | N
585858      | 214566     | 2022-04-04 00:00:00 |  16:30:12         | N
585858      | 214566     | 2022-04-04 00:00:00 |  16:30:12         | P
585858      | 963247     | 2022-04-04 00:00:00 |  17:51:15         | P
585858      | 963247     | 2022-04-04 00:00:00 |  17:51:15         | P
585858      | 963247     | 2022-04-04 00:00:00 |  17:51:15         | F
585858      | 963247     | 2022-04-04 00:00:00 |  17:51:15         | F
124578      | 852369     | 2022-04-06 00:00:00 |  12:20:01         | P
124578      | 852369     | 2022-04-06 00:00:00 |  12:20:01         | P
124578      | 852369     | 2022-04-06 00:00:00 |  12:20:01         | P
124578      | 852369     | 2022-04-06 00:00:00 |  12:20:01         | P
124578      | 148623     | 2022-04-07 00:00:00 |  10:51:48         | F
124578      | 148623     | 2022-04-07 00:00:00 |  10:51:48         | N
124578      | 148623     | 2022-04-07 00:00:00 |  10:51:48         | F
124578      | 148623     | 2022-04-07 00:00:00 |  10:51:48         | F

Expected Result

RN_TEST_ID  | RN_RUN_ID  | RN_EXECUTION_DATE   | RN_EXECUTION_TIME | ST_STATUS
585858      | 963247     | 2022-04-04 00:00:00 |  17:51:15         | P
585858      | 963247     | 2022-04-04 00:00:00 |  17:51:15         | P
585858      | 963247     | 2022-04-04 00:00:00 |  17:51:15         | F
585858      | 963247     | 2022-04-04 00:00:00 |  17:51:15         | F
124578      | 148623     | 2022-04-07 00:00:00 |  10:51:48         | F
124578      | 148623     | 2022-04-07 00:00:00 |  10:51:48         | N
124578      | 148623     | 2022-04-07 00:00:00 |  10:51:48         | F
124578      | 148623     | 2022-04-07 00:00:00 |  10:51:48         | F

Can you please help me to get this result?

Many thanks in advance.

UPDATE 1

As I can't use CTE as I'm doing queries into HPALM, I tried to use a sub-request.

SELECT RN_TEST_ID, RN_RUN_ID, RN_EXECUTION_DATE, RN_EXECUTION_TIME, ST_STATUS
FROM (
SELECT RANK() OVER (PARTITION BY RN_TEST_ID ORDER BY RN_EXECUTION_DATE, RN_EXECUTION_TIME) AS DR, RUN.*
FROM RUN
) AS REQ
INNER JOIN STEP ON REQ.RN_RUN_ID = STEP.ST_RUN_ID
WHERE DR = 1 AND REQ.RN_ASSIGN_RCYC = '1100'.

However, I'm geeting all records instead of getting the most recent records per TEST_ID (based on Exec Date and Exec Time) and I don't understand why ...

Can you please help me?

Solution

  • Use RANK here:

    WITH cte AS (
    SELECT *, RANK() OVER (PARTITION BY RN_TEST_ID
                           ORDER BY RN_EXECUTION_DATE DESC, RN_EXECUTION_TIME DESC) rnk
    FROM yourTable
)

SELECT RN_TEST_ID, RN_RUN_ID, RN_EXECUTION_DATE, RN_EXECUTION_TIME, ST_STATUS
FROM cte
WHERE rnk = 1;

    Note that it is usually preferable to store date and time as a single datetime/timestamp column. You may wish to change your design at some point.