Compare End Date of the first record with Start Date of the second

Asked

Viewed 345 times

1

Hello.

I’m having trouble drafting a SELECT that checks whether the End Date of the first query record is greater than the start date of the second.

The database in question is of notes and for some reason, some notes were with the start date smaller than the previous end date.

To illustrate better I will use the terms Attendance, Pending and Time, remembering that a call can contain more than one pending and a pending may contain more than one time.

inserir a descrição da imagem aqui

The intention is to identify which times within a particular service that are with the end date longer than the start date of the next record.

This only within the service, that is, can not be compared for example the end date of CDTEMPO 256261 with the start date of CDTEMPO 256269. Why they are different calls.

I need ideas on how to identify these times. It doesn’t have to be necessarily by SELECT, it could be by Procedure also without problems. In this select or precise precedent it would be displayed the same fields as the image above, and return only the record that has the start date less than the end date of the previous record.

In theory select/Procedure should return this to me:

inserir a descrição da imagem aqui

Thanks in advance!

sql query firebird firebird-2.5

  • What marks the registry order ? I would try a Join with the table itself , something like key=key-1

  • Check any overlaps within a call or within a call/pending?

1 answer

0

You can check the record by passing the start date of the customer service and the end date of the previous customer service. All returns (value) >= (00:00:00) are records that do not cancel the time of the end date - 1 with the current start date. Records that return (value) < (00:00:00) are records that have time constraints.

SELECT TIMEDIFF(STR_TO_DATE( "01/05/2014 09:00:00", "%d/%m/%Y %H:%i:%s" ), 
STR_TO_DATE( "01/05/2014 09:00:00", "%d/%m/%Y %H:%i:%s" )) diferenca

SELECT TIMEDIFF(STR_TO_DATE( "01/05/2014 11:00:00", "%d/%m/%Y %H:%i:%s" ), 
STR_TO_DATE( "01/05/2014 10:00:00", "%d/%m/%Y %H:%i:%s" )) diferenca

SELECT TIMEDIFF(STR_TO_DATE( "01/05/2014 12:00:00", "%d/%m/%Y %H:%i:%s" ), 
STR_TO_DATE( "01/05/2014 13:00:00", "%d/%m/%Y %H:%i:%s" )) diferenca

Browser other questions tagged sql query firebird firebird-2.5

You are not signed in. Login or sign up in order to post.