Filtering Time range in Time field for records (1 Viewer)

AlexTeslin · Aug 13, 2008

Hi,

I am trying to filter those records whith overlap times only. For example:

field1 TimeStart TimeFinish
rec1 13:00 15:00
rec2 14:00 14:30
rec3 15:00 16:00
rec4 14:30 15:00

This should output rec1, rec2 and rec4. But not rec3, because no other records overlap from 15:00 to 16:00. I can check distinguish between them if they would start at the same time, for example, but that is not enough.

Any suggestions would be greatly appreciated,

thank you

dallr · Aug 13, 2008

Does your table have a primary key. If it does you can use the following SQL. I am also attached a sample db.

Code:

SELECT YT1.*
FROM 
    (SELECT * , 
     (SELECT count(*) FROM Yourtable Y WHERE Y.ID<=Yourtable.ID) AS DerivedAuto
    FROM YourTable) YT1 

  LEFT JOIN 

     (SELECT * ,
      (SELECT count(*) FROM Yourtable Y WHERE Y.ID<=Yourtable.ID) AS DerivedAuto
     FROM YourTable) YT2 

    ON YT1.DerivedAuto = (YT2.DerivedAuto -1)
WHERE  iif(YT1.TimeStart Between YT2.TimeStart AND YT2.TimeFinish,1,0)<>1

AlexTeslin · Aug 18, 2008

Thank you Dallr for your reply. I have used your query, but for some reason when I am trying to run - it hungs and never produces any result. Is there anything I am doing wrong?

dallr · Aug 18, 2008

What do you mean it gangs? Is it taking to long to run? Have you tested it with a small amount of records first?

Dallr

AlexTeslin · Aug 18, 2008

I have not tested on my small table, but I have downloaded your file and it works. (One thing though - when I click to design view, it would not allow to switch).

The last time I tried, it went running, but froze again - i have waited for more than 10 minutes, so never had a chance to see the results.

Sorry for asking beginners questions, but is this code loops through records? And I am running on a query which I had already build and primary key on that query is a 5 digit number. Do you think i should create a new field with a primary key started with 1, for example.

dallr · Aug 18, 2008

JET which is the database engine that access uses is not smart enough at times. These types of queries do not show in design view. Therefore ,you have to handle all your edits in SQL View.

I don't understand you last question.? Can you please explain again.

dallr

AlexTeslin · Aug 19, 2008

JET which is the database engine that access uses is not smart enough at times. These types of queries do not show in design view. Therefore ,you have to handle all your edits in SQL View.

I don't understand you last question.? Can you please explain again.

dallr

Thanks for your reply, its fine - I do not really need design view if i make the query work.

Second quesion: I mean this line for example: "count (*)..." I am not too sure what fields this counts.
I am trying to create a query which monitors the bookings been done.

I meant I already have a query which i need to run this query on top. For example the existent query generates only those records which appear say more than 5 times a day. I need to run new query which will only show the bookings that overlaps in time (the one i am trying to create now). So the existent query has booking_ref as a primary key abd its unique, but it is a 5 digit number such as 46864.

I hope I made more sense.

Thanks in advance

AlexTeslin · Aug 19, 2008

I have added more records into a sample table and produces the wrong results. It runs OK with 4 records which you have created, but when i added 2 more records with no time overlaps to the table and run the query, it shows those records as well, though it should not.

I will be greatful if you could let me know why?

Thank you

dallr · Aug 19, 2008

OK, lets start over from the top by reexamining you first post.
1. You said the output should be Rec1,Rec2 & Rec4. But your output should really be Rec2 & Rec4, simply because Rec1 does not have a record before it as a reference point to see if 13:00 is between it.
2. Your start time of Rec4 is not between the start and finish times of Rec3, but it is actually lower than it. I thought was

"you were looking for all next records where the startTime of that record was between the starttime and finishTime of the previous record".

But you are looking for a bit more. In addition to what i just quoted you are also looking for

any current record start time that is lower than the previous record start time

.

If my comments and assumptions are correct then this should be more ideal.

Code:

SELECT YT1.*
FROM 
   (SELECT *, (SELECT count(*) FROM Yourtable Y WHERE Y.ID<=Yourtable.ID) AS DerivedAuto FROM YourTable) AS YT1 
LEFT JOIN
    (SELECT * , (SELECT count(*) FROM Yourtable Y WHERE Y.ID<=Yourtable.ID) AS DerivedAuto FROM YourTable) AS YT2 
  ON YT1.DerivedAuto = (YT2.DerivedAuto +1)
WHERE iif((YT1.TimeStart   Between YT2.TimeStart AND YT2.TimeFinish) OR YT1.TimeStart <=YT2.TimeStart,1,0)=1;

Dallr

dallr · Aug 19, 2008

If my comment in point 2 above holds true you can even use this in your WHERE clause and replace what I had previously.

Code:

WHERE YT1.TimeStart <=YT2.TimeFinish

dallr

AlexTeslin · Aug 19, 2008

I am so sorry if I made a mess here. In fact what I am really looking is more than that. I am looking for any record which is between the start and finish times with any other records. Not to only adjacent records but each record to be comparable to all other ones. I also have date field and what I wanted was to amend the query so that this function on overlapping times would only apply for a particular day (like group by).

In a programming language this probably could have been done by nested loop. But as I statied Java and not VB or VBA, I thought it might be possible to implement with SQL rather than studying VB.

Do you think this is feasible solution in SQL?

Again, my apologies for not being clear.

Thank you

Filtering Time range in Time field for records (1 Viewer)

AlexTeslin

Registered User.

dallr

AWF VIP

Attachments

AlexTeslin

Registered User.

dallr

AWF VIP

AlexTeslin

Registered User.

dallr

AWF VIP

AlexTeslin

Registered User.

AlexTeslin

Registered User.

dallr

AWF VIP

dallr

AWF VIP

AlexTeslin

Registered User.

Similar threads

Users who are viewing this thread