Home » SQL & PL/SQL » SQL & PL/SQL » Pick One : A Query for Multiple Submission of form but to take only first one. (Oracle 11g R2)
Pick One : A Query for Multiple Submission of form but to take only first one. [message #653141] |
Tue, 28 June 2016 07:15 |
|
Guru's,
I have one scenario where i am stuck with.
I have a table which is associated with a form , form have number of status of tickets . User can either change the status and submit or update just comment and submit on same status. Here how the table looks.
CREATE TABLE "TICKETS"
( "TICKET_ID" VARCHAR2(4000 CHAR),
"CUSTOMER_ID" NUMBER,
"CREATION_DATE" DATE,
"STATUS_ID" NUMBER,
"PROCESS_INSTANCE_ID" NUMBER,
"CLOSE_STATUS_ID" NUMBER
) ;
The data of this form looks like this.
SET DEFINE OFF;
INSERT INTO tickets
(ticket_id, customer_id,
creation_date, status_id,
process_instance_id, close_status_id
)
VALUES ('IS2016060046', 237,
TO_DATE ('02-JUN-16 02.16.57 AM', 'DD-MON-RR HH.MI.SS AM'), 1,
124970, 8
);
INSERT INTO tickets
(ticket_id, customer_id,
creation_date, status_id,
process_instance_id, close_status_id
)
VALUES ('IS2016060046', 237,
TO_DATE ('02-JUN-16 02.20.28 AM', 'DD-MON-RR HH.MI.SS AM'), 1,
124970, 8
);
INSERT INTO tickets
(ticket_id, customer_id,
creation_date, status_id,
process_instance_id, close_status_id
)
VALUES ('IS2016060046', 237,
TO_DATE ('02-JUN-16 02.20.28 AM', 'DD-MON-RR HH.MI.SS AM'), 2,
124970, 8
);
INSERT INTO tickets
(ticket_id, customer_id,
creation_date, status_id,
process_instance_id, close_status_id
)
VALUES ('IS2016060046', 237,
TO_DATE ('02-JUN-16 02.22.35 AM', 'DD-MON-RR HH.MI.SS AM'), 3,
124970, 8
);
INSERT INTO tickets
(ticket_id, customer_id,
creation_date, status_id,
process_instance_id, close_status_id
)
VALUES ('IS2016060046', 237,
TO_DATE ('02-JUN-16 02.23.34 AM', 'DD-MON-RR HH.MI.SS AM'), 4,
124970, 8
);
INSERT INTO tickets
(ticket_id, customer_id,
creation_date, status_id,
process_instance_id, close_status_id
)
VALUES ('IS2016060046', 237,
TO_DATE ('02-JUN-16 02.24.47 AM', 'DD-MON-RR HH.MI.SS AM'), 5,
124970, 8
);
INSERT INTO tickets
(ticket_id, customer_id,
creation_date, status_id,
process_instance_id, close_status_id
)
VALUES ('IS2016060046', 237,
TO_DATE ('02-JUN-16 02.25.40 AM', 'DD-MON-RR HH.MI.SS AM'), 5,
124970, 8
);
INSERT INTO tickets
(ticket_id, customer_id,
creation_date, status_id,
process_instance_id, close_status_id
)
VALUES ('IS2016060046', 237,
TO_DATE ('02-JUN-16 02.26.39 AM', 'DD-MON-RR HH.MI.SS AM'), 6,
124970, 8
);
INSERT INTO tickets
(ticket_id, customer_id,
creation_date, status_id,
process_instance_id, close_status_id
)
VALUES ('IS2016060046', 237,
TO_DATE ('02-JUN-16 02.27.21 AM', 'DD-MON-RR HH.MI.SS AM'), 7,
124970, 8
);
INSERT INTO tickets
(ticket_id, customer_id,
creation_date, status_id,
process_instance_id, close_status_id
)
VALUES ('IS2016060046', 237,
TO_DATE ('02-JUN-16 02.28.56 AM', 'DD-MON-RR HH.MI.SS AM'), 8,
124970, 8
);
Now i query
and getting data like this
TICKET_ID CUSTOMER_ID CREATION_DATE STATUS_ID PROCESS_INSTANCE_ID CLOSE_STATUS_ID
------------------------------------------------------------------------------------------------
IS2016060046 237 6/2/2016 2:16:57 AM 1 124970 8
IS2016060046 237 6/2/2016 2:20:28 AM 1 124970 8
IS2016060046 237 6/2/2016 2:20:28 AM 2 124970 8
IS2016060046 237 6/2/2016 2:22:35 AM 3 124970 8
IS2016060046 237 6/2/2016 2:23:34 AM 4 124970 8
IS2016060046 237 6/2/2016 2:24:47 AM 5 124970 8
IS2016060046 237 6/2/2016 2:25:40 AM 5 124970 8
IS2016060046 237 6/2/2016 2:26:39 AM 6 124970 8
IS2016060046 237 6/2/2016 2:27:21 AM 7 124970 8
IS2016060046 237 6/2/2016 2:28:56 AM 8 124970 8
The problem i have here is like ticket number "IS2016060046" was submitted twice for status 1 but i wont be able to calculate exact time spent on the ticket for that status because of two rows of data. I will need only first submission of every ticket number and not others. The older submission.
I am working on that and will post my statements which i am trying in plsql.
Thanks
Javed
[Updated on: Tue, 28 June 2016 07:17] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
Re: Pick One : A Query for Multiple Submission of form but to take only first one. [message #653400 is a reply to message #653194] |
Mon, 04 July 2016 13:06 |
|
I did this.
SELECT TICKET_ID, ROUND( SUM(MINUTES),2) AS TOTAL_TIME_MIN, STATUS_ID FROM (SELECT TICKET_ID,STATUS_ID,CREATION_DATE,
LEAD (CREATION_DATE) OVER (ORDER BY CREATION_DATE) LEAD_TIME,
(LEAD (CREATION_DATE) OVER (ORDER BY CREATION_DATE) - CREATION_DATE)*24*60 MINUTES
FROM ( SELECT * FROM MS_DW_TICKETS_LC_DETAIL WHERE TICKET_ID='Some_Id'
ORDER BY CREATION_DATE,CUSTOMER_ID, status_id) A) GROUP BY STATUS_ID, TICKET_ID;
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:11:58 CDT 2024
|