ralf.butler@web.de
Newbie
Joined: 14 Jun 2009
Posts: 3
|
 Posted: Sun Jun 14, 2009 9:24 pm Post subject: Complex date/time related query |
 |
|
Dear All,
I have some troubles to set up the right query in base.
I have two tables. Each tables have one column for date and another for time. In addition, table A has a further column which reflects a timespan in minutes. The content looks as follows:
| Code: |
tableA
idA | dateA | timeA | spanA
----|------------|----------|------
1 | 01/01/2009 | 13:45:00 | 78
2 | 02/01/2009 | 09:25:00 | 14
3 | 03/01/2009 | 07:55:00 | 11
4 | 06/01/2009 | 19:25:00 | 91
|
| Code: |
tableB
idB | dateB | timeB
----|------------|---------
1 | 01/01/2009 | 13:51:00
2 | 01/01/2009 | 14:16:00
3 | 02/01/2009 | 09:26:00
4 | 06/01/2009 | 19:46:00
5 | 08/01/2009 | 23:56:00
|
Now, I want to create a query which returns idA and idB where
(dateA & timeA) <= (dateB & timeB) AND
(dateA & timeA) + spanA >= (dateB & timeB)
In my example the result should be the following:
| Code: |
idA | idB
----|----
1 | 1
1 | 2
2 | 3
4 | 4
|
I've already difficulties to merge the date and time columns. Well, it works with the CONCAT function but this is really cumbersome. Is there any other method? Since this seems to be a minor problem, how to add the timespan (minutes) to a date & time?
It would be great if someone could help me to set up the right query.
Thank you very much,
Ralf |
|
