I have a table like this
| Emplid | REQUEST_ID | Status | Status_Dttm |
|---|---|---|---|
| 1234 | 1 | Open | 02-Jun-2022 12.35.00.AM |
| 1231 | 5 | Draft | 02-Jun-2022 12.30.00.AM |
| 1234 | 1 | In Progress | 02-Jun-2022 12.47.00.AM |
How can I get request date as Min(Status Dttm) and current status as Max(Status Dttm) of every request
I tried using this SELECT EMPLID,REQUEST_ID,MIN(Status_Dttm) AS "REQUEST DATE", (SELECT STATUS FROM STS_TBL WHERE MAX(Status_Dttm)) AS "CURRENT_STATUS" FROM STS_TBL;
getting error as "Group function is not allowed here"
You want to add GROUP BY request_id if you want to group by each request:
SELECT MIN(emplid) AS emplid,
request_id,
MIN(status_dttm) AS request_date,
MAX(status) KEEP (DENSE_RANK LAST ORDER BY status_dttm) AS current_status
FROM sts_tbl
GROUP BY request_id
Which outputs:
EMPLID REQUEST_ID REQUEST_DATE CURRENT_STATUS 1234 1 2022-06-02 00:35:00 In Progress 1231 5 2022-06-02 00:30:00 Draft
If the request_id is the same for each emplid (which appears to be the case for your sample data) then you could use:
SELECT emplid,
MIN(status_dttm) AS request_date,
MAX(status) KEEP (DENSE_RANK LAST ORDER BY status_dttm) AS current_status
FROM sts_tbl
GROUP BY emplid
Which outputs:
EMPLID REQUEST_DATE CURRENT_STATUS 1231 2022-06-02 00:30:00 Draft 1234 2022-06-02 00:35:00 In Progress
db<>fiddle here
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments