Hi All,
Thank you in Advance. I have a table TAB with below fields. The table maintains all PID's with their status per day wise. Each time user start a process in day, it will insert a record with status (Flag = Y/N). Same user can try same process multiple times in that day, so multiple records on same day with same PID and UID possible, and a user/pid from previous day can be retried next day as well so same PID/UID with new date inserted. There are few PIDs failed (PRC_FLAG = N) for past week along with some SUCCESS (PRC_FLAG = Y) one's as well.
I am able to find the distinct PID and distinct UID per day (query below group by date, but that have duplicates if same user retries next dates) however I am trying to find distinct PIDs/UIDs for past week if a user/PID has atleast one failure.
CREATE TABLE TAB (SEQ_ID NUMBER (4), PID VARCHAR2(10),UID VARCHAR2(10), PRC_DATE DATE, PRC_FLAG CHAR(1));
INSERT INTO TAB VALUES (1234, '1111','user1', TO_DATE('02/14/2019','MM/DD/YYYY'), 'N');
INSERT INTO TAB VALUES (1235, '2222','user1', TO_DATE('02/14/2019','MM/DD/YYYY'), 'N');
INSERT INTO TAB VALUES (1236, '1111','user1', TO_DATE('02/14/2019','MM/DD/YYYY'), 'N');
INSERT INTO TAB VALUES (1237, '2222','user1', TO_DATE('02/15/2019','MM/DD/YYYY'), 'N');
INSERT INTO TAB VALUES (1238, '3333','user3', TO_DATE('02/15/2019','MM/DD/YYYY'), 'N');
INSERT INTO TAB VALUES (1239, '1111','user1', TO_DATE('02/15/2019','MM/DD/YYYY'), 'N');
INSERT INTO TAB VALUES (1240, '4444','user4', TO_DATE('02/16/2019','MM/DD/YYYY'), 'N');
INSERT INTO TAB VALUES (1241, '2222','user1', TO_DATE('02/16/2019','MM/DD/YYYY'), 'N');
INSERT INTO TAB VALUES (1242, '5555','user2', TO_DATE('02/14/2019','MM/DD/YYYY'), 'N');
INSERT INTO TAB VALUES (1243, '6666','user2', TO_DATE('02/14/2019','MM/DD/YYYY'), 'Y');
My query is below:
SELECT PRC_FLAG, TRUNC (PRC_DATE),COUNT (DISTINCT PID),COUNT (UID)
FROM TAB
WHERE PRC_FLAG = 'N'
AND TRUNC (PRC_DATE) >= TRUNC (SYSDATE - 7)
GROUP BY PRC_FLAG, TRUNC (PRC_DATE)
BY 2;
Current Results:
PRC_DATE PID UID
02/14 3 2
02/15 3 2
02/16 2 2
Expected Results:
PRC_DATE PID UID
02/14 3 2
02/15 1 1 (2222,1111 already counted on 02/14, so ignore counting same PID/UID for 02/15)
02/16 1 1
In other words : select count of distinct UID, count of distinct PID per day, Pick the min prc_date per UID/PID if same UID and PID appears on multiple day.
apologies for any confusion.