Home » SQL & PL/SQL » SQL & PL/SQL » Find two or more consecutive dates
Find two or more consecutive dates [message #444723] Tue, 23 February 2010 14:36 Go to next message
justij
Messages: 3
Registered: February 2010
Junior Member
For the life of me I can not understand how to do this. Any help would be greatly appreciated.

I am pulling information from a view to get the current results and placing them into a cursor which then inserts the results into a temp table.


Current Results:
JOB_NAME SCHEDULED_TIME STATUS
JOB_1 02/23/2010 13:25:00 Failed
JOB_1 02/22/2010 13:25:00 Failed
JOB_1 02/21/2010 13:25:00 Failed
JOB_1 02/19/2010 13:25:00 Failed
JOB_2 02/21/2010 13:25:00 Failed
JOB_2 02/18/2010 13:25:00 Failed
JOB_2 02/17/2010 13:25:00 Error
JOB_2 02/15/2010 13:25:00 Error

Needed Results:
JOB_NAME SCHEDULED_TIME STATUS
JOB_1 02/23/2010 13:25:00 Failed
JOB_1 02/22/2010 13:25:00 Failed
JOB_1 02/21/2010 13:25:00 Failed
JOB_2 02/18/2010 13:25:00 Failed
JOB_2 02/17/2010 13:25:00 Error

Thanks,

Justin



Re: Find two or more consecutive dates [message #444727 is a reply to message #444723] Tue, 23 February 2010 14:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data, and explain the result with words.

Regards
Michel

[Updated on: Tue, 23 February 2010 14:51]

Report message to a moderator

Re: Find two or more consecutive dates [message #444731 is a reply to message #444723] Tue, 23 February 2010 15:51 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>then inserts the results into a temp table.

In some/many/most cases in Oracle "temp" table are superfluous.

We don't have your table(s).
We don't have your data.
We don't have your SQL.
We don't know your requirements.

Without additional details, You're On Your Own (YOYO!).


Re: Find two or more consecutive dates [message #444806 is a reply to message #444723] Wed, 24 February 2010 05:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To quote Tom Kyte - analytics rock, analytics roll...
with src as (select 'JOB_1' job_name ,to_date('02/23/2010 13:25:00','mm/dd/yyyy hh24:mi:ss') scheduled_time ,'Failed' status from dual union all
select 'JOB_1',to_date('02/22/2010 13:25:00','mm/dd/yyyy hh24:mi:ss'),'Failed' from dual union all
select 'JOB_1',to_date('02/21/2010 13:25:00','mm/dd/yyyy hh24:mi:ss'),'Failed' from dual union all
select 'JOB_1',to_date('02/19/2010 13:25:00','mm/dd/yyyy hh24:mi:ss'),'Failed' from dual union all
select 'JOB_2',to_date('02/21/2010 13:25:00','mm/dd/yyyy hh24:mi:ss'),'Failed' from dual union all
select 'JOB_2',to_date('02/18/2010 13:25:00','mm/dd/yyyy hh24:mi:ss'),'Failed' from dual union all
select 'JOB_2',to_date('02/17/2010 13:25:00','mm/dd/yyyy hh24:mi:ss'),'Error' from dual union all
select 'JOB_2',to_date('02/15/2010 13:25:00','mm/dd/yyyy hh24:mi:ss'),'Error' from dual)
select job_name
      ,scheduled_time
      ,status
from  (select job_name
             ,scheduled_time
             ,status
             ,lead(scheduled_time) over (partition by job_name order by scheduled_time asc) lead_time
             ,lag(scheduled_time) over (partition by job_name order by scheduled_time asc)  lag_time
       from   src)
where lead_time = scheduled_time+1
or    lag_time  = scheduled_time-1


[fixed minor bug with code]

[Updated on: Wed, 24 February 2010 05:32]

Report message to a moderator

Re: Find two or more consecutive dates [message #444833 is a reply to message #444806] Wed, 24 February 2010 07:28 Go to previous messageGo to next message
justij
Messages: 3
Registered: February 2010
Junior Member
Thank you very very much! That worked perfectly!

[Updated on: Wed, 24 February 2010 08:13] by Moderator

Report message to a moderator

Re: Find two or more consecutive dates [message #444844 is a reply to message #444833] Wed, 24 February 2010 08:14 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time please follow the guide and provide a test case.

Regards
Michel
Previous Topic: highest value
Next Topic: create new column
Goto Forum:
  


Current Time: Wed Dec 07 23:59:18 CST 2016

Total time taken to generate the page: 0.15041 seconds