Home » SQL & PL/SQL » SQL & PL/SQL » Issue with a query
Issue with a query [message #244586] Wed, 13 June 2007 07:52 Go to next message
daniel.nicula
Messages: 6
Registered: June 2007
Junior Member
This is my situation:

    event_type  event_date       user                  result

5	A	3/19/2007	U1            	        OK
13	B	3/19/2007	U1             	
4	A	3/20/2007	U1            	        OK
9	A	3/21/2007	U1             	        OK
6	A	3/22/2007	U2             	        OK
2	A	3/23/2007	U2             	        Warning
1	A	3/26/2007	U3             	        OK
3	A	3/27/2007	U1             	        OK
8	A	3/28/2007	U3             	        OK
12	B	3/29/2007	U2             	
11	C	3/29/2007	U2            	
7	A	3/29/2007	U3             	        OK
10	C	3/29/2007	U2             	


How can i obtain the last interval of time in which an event occured continously with the same result?
For example between 26 and 29 march 2007 occured the result OK for event A, between 29 and 29 march 2007(only on 29) occured event B, and between 29 and 29 march 2007 (only on 29)occured event C.

Thank You.

[Updated on: Wed, 13 June 2007 08:33] by Moderator

Report message to a moderator

Re: Issue with a query [message #244607 is a reply to message #244586] Wed, 13 June 2007 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please always post your Oracle version (4 decimals).
Please read and follow How to format your posts.
If you post create table and insert statements a test case, it will be easier to find and show you a solution.

Regards
Michel

[Updated on: Wed, 13 June 2007 08:55]

Report message to a moderator

Re: Issue with a query [message #245545 is a reply to message #244607] Mon, 18 June 2007 01:56 Go to previous messageGo to next message
daniel.nicula
Messages: 6
Registered: June 2007
Junior Member
This is the create table and insert scripts
create table TEST
(
  EVENTTYPE  VARCHAR2(2),
  EVENT_DATE DATE,
  USER_NAME      VARCHAR2(2),
  RESULT_OBTAINED     VARCHAR2(10)
)

INSERT INTO test VALUES ('A', to_date('19-MAR-2007'), 'U1', 'OK');
INSERT INTO test VALUES ('B', to_date('19-MAR-2007'), 'U1', NULL);
INSERT INTO test VALUES ('A', to_date('20-MAR-2007'), 'U1', 'OK');
INSERT INTO test VALUES ('A', to_date('21-MAR-2007'), 'U1', 'OK');
INSERT INTO test VALUES ('A', to_date('22-MAR-2007'), 'U2', 'OK');
INSERT INTO test VALUES ('A', to_date('23-MAR-2007'), 'U2', 'Warning');
INSERT INTO test VALUES ('A', to_date('26-MAR-2007'), 'U3', 'OK');
INSERT INTO test VALUES ('A', to_date('27-MAR-2007'), 'U1', 'OK');
INSERT INTO test VALUES ('A', to_date('28-MAR-2007'), 'U3', 'OK');
INSERT INTO test VALUES ('B', to_date('29-MAR-2007'), 'U2', NULL);
INSERT INTO test VALUES ('C', to_date('29-MAR-2007'), 'U2', NULL);
INSERT INTO test VALUES ('A', to_date('29-MAR-2007'), 'U3', 'OK');
INSERT INTO test VALUES ('C', to_date('29-MAR-2007'), 'U2', NULL);
commit;


Thank you for reminding me!



Re: Issue with a query [message #245567 is a reply to message #245545] Mon, 18 June 2007 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With the data you posted:
SQL> select eventtype, event_date, result_obtained from test order by 1, 2;
EV EVENT_DATE  RESULT_OBT
-- ----------- ----------
A  19-MAR-2007 OK
A  20-MAR-2007 OK
A  21-MAR-2007 OK
A  22-MAR-2007 OK
A  23-MAR-2007 Warning
A  26-MAR-2007 OK
A  27-MAR-2007 OK
A  28-MAR-2007 OK
A  29-MAR-2007 OK
B  19-MAR-2007
B  29-MAR-2007
C  29-MAR-2007
C  29-MAR-2007

13 rows selected.

SQL> with 
  2    data as (
  3      select EVENTTYPE, EVENT_DATE, RESULT_OBTAINED,
  4             case when nvl(lag(RESULT_OBTAINED) 
  5                             over (partition by eventtype order by event_date), 
  6                           '@')
  7                       != result_obtained
  8                    then rownum
  9             end flag
 10      from test
 11    ),
 12    groups as (
 13      select EVENTTYPE, EVENT_DATE, RESULT_OBTAINED,
 14             max(flag) over (partition by eventtype order by event_date) grp
 15      from data
 16    )
 17  select eventtype, 
 18         min(event_date) first_date,
 19         max(event_date) last_date,
 20         result_obtained
 21  from groups
 22  group by eventtype, result_obtained, grp
 23  order by 1, 2
 24  /
EV FIRST_DATE  LAST_DATE   RESULT_OBT
-- ----------- ----------- ----------
A  19-MAR-2007 22-MAR-2007 OK
A  23-MAR-2007 23-MAR-2007 Warning
A  26-MAR-2007 29-MAR-2007 OK
B  19-MAR-2007 29-MAR-2007
C  29-MAR-2007 29-MAR-2007

5 rows selected.

Regards
Michel
Re: Issue with a query [message #245763 is a reply to message #245567] Mon, 18 June 2007 13:20 Go to previous messageGo to next message
daniel.nicula
Messages: 6
Registered: June 2007
Junior Member
Thank you kindly for your interesting solution.
But i would need to take out from this results only the last interval for every event_type, meaning:

A 26-MAR-2007 29-MAR-2007 OK
B 19-MAR-2007 29-MAR-2007
C 29-MAR-2007 29-MAR-2007


Regards,
Daniel
Re: Issue with a query [message #246182 is a reply to message #245763] Wed, 20 June 2007 03:57 Go to previous messageGo to next message
daniel.nicula
Messages: 6
Registered: June 2007
Junior Member
when i'm trying to show the user too in my result, it forces me to put it in the group by also, and this is changing my result.
How cand i show the user without changing the result.
Re: Issue with a query [message #246191 is a reply to message #246182] Wed, 20 June 2007 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is normal, you have to say which user you want in the group, the first one, the last one, one in the middle between first_date and and_date?

Regards
Michel


Re: Issue with a query [message #246224 is a reply to message #244586] Wed, 20 June 2007 05:57 Go to previous messageGo to next message
daniel.nicula
Messages: 6
Registered: June 2007
Junior Member
I would need to show the last user from the interval, so that one who got the event in the last day. Meaning:

A 26-MAR-2007 29-MAR-2007 OK U3
B 19-MAR-2007 29-MAR-2007 U2
C 29-MAR-2007 29-MAR-2007 U2

Kind regards,
Daniel
Re: Issue with a query [message #246230 is a reply to message #246224] Wed, 20 June 2007 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    data as (
  3      select EVENTTYPE, EVENT_DATE, RESULT_OBTAINED,
  4             case when lead(eventtype) over (partition by eventtype order by event_date)
  5                         is null
  6                    or nvl(lead(RESULT_OBTAINED) 
  7                             over (partition by eventtype order by event_date), 
  8                           '@')
  9                       != nvl(result_obtained,'@')
 10                  then user_name
 11             end user_name,
 12             case when nvl(lag(RESULT_OBTAINED) 
 13                             over (partition by eventtype order by event_date), 
 14                           '@')
 15                       != result_obtained
 16                    then rownum
 17             end flag
 18      from test
 19    ),
 20    groups as (
 21      select EVENTTYPE, EVENT_DATE, RESULT_OBTAINED, flag, user_name,
 22             max(flag) over (partition by eventtype order by event_date) grp
 23      from data
 24    )
 25  select eventtype, 
 26         min(event_date) first_date,
 27         max(event_date) last_date,
 28        result_obtained,
 29        min(user_name) user_name
 30  from groups
 31  group by eventtype, result_obtained, grp
 32  order by 1, 2
 33  /
EV FIRST_DATE  LAST_DATE   RESULT_OBT US
-- ----------- ----------- ---------- --
A  19-MAR-2007 22-MAR-2007 OK         U2
A  23-MAR-2007 23-MAR-2007 Warning    U2
A  26-MAR-2007 29-MAR-2007 OK         U3
B  19-MAR-2007 29-MAR-2007            U2
C  29-MAR-2007 29-MAR-2007            U2

5 rows selected.

Regards
Michel
Re: Issue with a query [message #246509 is a reply to message #244586] Thu, 21 June 2007 03:19 Go to previous messageGo to next message
daniel.nicula
Messages: 6
Registered: June 2007
Junior Member
Yes, thank you!


Kind regards
Daniel
Re: Issue with a query [message #246523 is a reply to message #246509] Thu, 21 June 2007 03:52 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think this is not the best way.
Study the query, there may be some enhancements.
The main problem is NULL in your columns.

Regards
Michel
Previous Topic: Row to column conversion
Next Topic: What way Procedure are better then Function for Select statement
Goto Forum:
  


Current Time: Sat Dec 03 10:13:43 CST 2016

Total time taken to generate the page: 6.41421 seconds