Hi,
Create table Matrix_Request
(
ID NUMBER(10,0),
MATRIX_REQUEST_ID VARCHAR2(255 CHAR) NOT NULL ENABLE,
"MATRIX_CLINICIAN_OWNER" VARCHAR2(255 CHAR),
"MATRIX_GROUP" VARCHAR2(255 CHAR),
"MATRIX_TYPE" VARCHAR2(255 CHAR)
)
Create table Matrix_ProcedureDetails
(
ID Number(10,0),
MATRIX_SETTING VARCHAR2(255 CHAR),
"REQUEST_PK" NUMBER(10,0)
)
Create table Matrix_Event_LOG
(
Request_PK NUMBER(10,0),
Procedure_details_PK NUMBER(10,0),
status VARCHAR2(255 CHAR),
EVENT_TIMESTAMP TIMESTAMP (6),
CUSTOM_6 VARCHAR2(256 BYTE),
EVENT_CODE VARCHAR2(32 CHAR)
)
INSERT INTO MATRIX_REQUEST VALUES
(1652313,'U19077AAAD','msClinician','ERS2','Group / ECCO');
INSERT INTO Matrix_ProcedureDetails VALUES(14567,'HCSCTS01',1652313);
INSERT INTO Matrix_ProcedureDetails VALUES(12897,'HCSCTS01',1652313);
INSERT INTO Matrix_ProcedureDetails VALUES(12887,'HCSCTS01',1652313);
INSERT INTO Matrix_ProcedureDetails VALUES(12893,'HCSCTS01',1652313);
INSERT INTO Matrix_ProcedureDetails VALUES(12896,'HCSCTS01',1652313);
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12893,'Pend','18-MAR-19 08.46.23.110000000 AM',NULL,'ASSI_CLI_ADD');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12897,'Pend','18-MAR-19 08.50.31.230000000 AM',NULL,'ASSI_CLI_ADD');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Pend','03-JUL-19 02.47.38.680000000 PM',NULL,'ASSI_REQ_FOR_EDI');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Pend','03-JUL-19 05.58.24.510000000 PM',NULL,'ASSI_REQ_FOR_EDI');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Pend','03-JUL-19 06.00.17.170000000 PM',NULL,'ASSI_REQ');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12896,'Pend','18-MAR-19 08.47.46.340000000 AM',NULL,'ASSI_CLI_ADD');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Pend','18-MAR-19 08.44.20.590000000 AM',NULL,'ASSI_REQ');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Pend','18-MAR-19 08.44.20.590000000 AM',NULL,'ASSI_REQ');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Pend','18-MAR-19 08.39.12.160000000 AM',NULL,'ASSI_NEW_REQ_INT');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Approve','16-SEP-19 05.41.11.480000000 PM','msClinician','REV');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Approve','16-SEP-19 05.41.24.530000000 PM','msClinician','ASSI_REQ');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Approve','16-SEP-19 05.41.51.480000000 PM','msClinician','DISPSTN_RQST');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Approve','16-SEP-19 05.41.54.360000000 PM','msClinician','ASSI_CLO_REQ');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Approve','16-SEP-19 05.41.54.360000000 PM','msClinician','ASSI_REQ');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Approve','16-SEP-19 06.04.41.550000000 PM',NULL,'MEMBER_OUTCOME');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Pend','16-SEP-19 05.28.30.570000000 PM',NULL,'MEMBER_OUTCOME');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Pend','16-SEP-19 05.36.48.120000000 PM',NULL,'MEMBER_OUTCOME');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Approve','16-SEP-19 05.40.02.430000000 PM','msClinician','REV_LET_GEN');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Approve','16-SEP-19 05.41.23.910000000 PM','msClinician','DISPSTN_RQST');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Approve','16-SEP-19 05.41.33.910000000 PM','msClinician','DISPSTN_RQST');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,14567,'Pend','16-SEP-19 05.41.23.910000000 PM',NULL,'MEMBER_OUTCOME');
Query:1
select Matrix_owner,Matrix_setting,Matrix_group,Matrix_type,CLOSED_REQUEST_COUNT from(
select Matrix_owner,Matrix_setting,Matrix_group,Matrix_type,CLOSED_REQUEST_COUNT,count(*) from
(Select Request.MATRIX_CLINICIAN_OWNER as Matrix_OWNER,
PROC.MATRIX_SETTING as Matrix_Setting,
Request.Matrix_GROUP As Matrix_GROUP,
Request.Matrix_TYPE as Matrix_TYPE,
PROC.ID,
(select count(*) from Matrix_EVENT_LOG Event1,Matrix_EVENT_LOG Event2 where
Request.id=Event1.Request_PK
and Request.id=Proc.REQUEST_PK
-- and PROC."ID"= Event1.PROCEDURE_DETAILS_PK
--AND Event.APPIAN_REQUEST_ID = 'U19077AAAD'
AND Event1.status <> 'Pend'
And Trunc(Event1."EVENT_TIMESTAMP")=trunc(Sysdate)-17
AND Event1.CUSTOM_6 = Request.MATRIX_CLINICIAN_OWNER
AND EVENT1.EVENT_CODE IN ('DISPSTN_RQST')
and Event1.Request_PK=Event2.Request_PK
and Event1.Procedure_details_PK=Event2.Procedure_details_PK
-- and Event1.Event_Timestamp>Event2.Event_Timestamp
AND Event2.status <> 'Pend'
And Trunc(Event2."EVENT_TIMESTAMP")=trunc(Sysdate)-17
AND Event2."CUSTOM_6" = Request."MATRIX_CLINICIAN_OWNER"
AND EVENT2."EVENT_CODE" IN ('DISPSTN_RQST')
) CLOSED_REQUEST_COUNT From Matrix_Request Request,
"Matrix_ProcedureDetails" PROC,
Matrix_Event_LOG Event
Where Request."MATRIX_REQUEST_ID" = 'U19077AAAD'
and Request.id=Event.Request_PK
and Request.id=Proc.REQUEST_PK
and PROC."ID"= Event.PROCEDURE_DETAILS_PK
)
group by Matrix_owner,Matrix_setting,Matrix_group,Matrix_type,CLOSED_REQUEST_COUNT)
Query:2
select * from MATRIX_EVENT_LOG Event where REQUEST_PK=1652313
AND Event.status <> 'Pend'
And Trunc(Event."EVENT_TIMESTAMP")=trunc(Sysdate)-17
-- AND Event.CUSTOM_6 = Request.MATRIX_CLINICIAN_OWNER
AND EVENT.EVENT_CODE IN ('DISPSTN_RQST')
My Requirement is I have to get Latest 1 record as closed_Request_count from Query1
Which satisfies data from Query2 as sample records with latest_timestamp as 16-SEP-19 05.41.51.480000000 PM for Request_pk=1652313
But Iam getting Output as 9.How to write Query Effectively and neately.Requesting your Immediate help
I have to get o/p in such a way that MATRIX_EVENT_LOG
for Event_code,Request_PK,Procedure_details_PK combination I have to get the latest record as CLOSED_REQUEST_COUNT
[Updated on: Thu, 03 October 2019 14:49] Report message to a moderator |
] 
Blog
Search
Help
Members
Register
Login
Home
