Home » SQL & PL/SQL » SQL & PL/SQL » Getting consecutive records satisfying a specific conditions  () 1 Vote
Getting consecutive records satisfying a specific conditions [message #262448] Mon, 27 August 2007 01:23 Go to next message
srp_naren
Messages: 3
Registered: August 2007
Junior Member
Hi,
Could anyone help me in resoving this problem?

I need to select all the consecutive records from a table that contains the difference of two date fields greater than 50.

Table:

Theatre | FILM | screen-In-date | out-date |

I need to select the films that has (out-date)-(screen-In-date) >50

The condition may be satisfied by a set of consecutive rows.(the theatre name can be different)

If the film name changes in a row, the condition fails.

I did this using a cursor and looping through it. Is there any better/simpler way of doing this???

Thanks,

[Updated on: Mon, 27 August 2007 01:34]

Report message to a moderator

Re: Getting consecutive records satisfying a specific conditions [message #262454 is a reply to message #262448] Mon, 27 August 2007 01:51 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Perhaps you can post some sample data + an expected result from that data. Or even better: a create table script + insert statements for that sample data.

MHE
Re: Getting consecutive records satisfying a specific conditions [message #262457 is a reply to message #262448] Mon, 27 August 2007 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course before:
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

In addition, define "consecutive".

Regards
Michel

[Updated on: Mon, 27 August 2007 01:59]

Report message to a moderator

Re: Getting consecutive records satisfying a specific conditions [message #262464 is a reply to message #262454] Mon, 27 August 2007 02:17 Go to previous messageGo to next message
srp_naren
Messages: 3
Registered: August 2007
Junior Member
sample data :

rownum| theatre | film | in-date | out-date|

1 |154 |123 | 3/8/2007 4:37:12 AM | 8/8/2007 4:43:57 AM
2 |154 |123 | 6/8/2007 4:17:53 AM | 8/8/2007 4:35:37 AM
3 |154 |122 | 6/8/2007 4:43:57 AM | 8/8/2007 4:44:34 AM
4 |154 |122 | 8/8/2007 4:35:37 AM | 8/8/2007 4:37:12 AM
5 |154 |123 | 8/17/2007 12:44:23 PM | 12/31/9999 11:59:59 PM

And we have a constraint here as if the film is not yet out ie., the date is 12/31/9999 11:59:59 PM, then we use sysdate as out-date

Expected Result :
At any point of concern, i need to get the rows 1 and 2 if the difference between out-date of row1 and in-date of row 2 (as film - 123 are same and consecutive for row 1 and 2) is greater than 50. Else i need to return latest film that satisfies the above condition along with the results for most recent film.

Thanks for spending your time on this.
Re: Getting consecutive records satisfying a specific conditions [message #262469 is a reply to message #262464] Mon, 27 August 2007 02:30 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Search the forum for LEAD() and/or LAG(). They are functions that allow you to peek at the next / previous row in an ordered set.

Ross Leishman
Re: Getting consecutive records satisfying a specific conditions [message #262473 is a reply to message #262464] Mon, 27 August 2007 02:40 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read again my post and follow it.

Regards
Michel
Previous Topic: Replace and underscores
Next Topic: Get DDL for advanced queus
Goto Forum:
  


Current Time: Sun Dec 11 04:12:28 CST 2016

Total time taken to generate the page: 0.24001 seconds