Linking Date and Time?????

[cobs]

I have a huge QUEST!!!
I have 2 tables.
T1--- SITE_ID, DATE, TIME, FLOW
T2--- SITE_ID, DATE, TIME, P.
I need to create a QUERY which allows me to link the two tables based on time and combine the FLOW and P.
QUERY--- SITE_ID, DATE, TIME, FLOW, P
However, the date and time do not exactly match in both tables
Any suggestions???

[gish]

maybe I don't understand but, why do you need two tables, why not put "P" in the first table?

[smaffey]

What Database format are you using?

[cobs]

The two tables already exist will all the data, so I cant use one table.... I just have to analyze the data.

Im using an Access database

[smaffey]

Assuming that the time is defined as some sort of time data type and that date is defined as some sort of date data type:

SELECT site_id
FROM T1, T2
where T1.DATE = T2.DATE and T2.TIME = T2.TIME;


Even though the data looks different visually, MS-Access treats long-time, short-time as the same thing. Now if the one of the fields was defined as string and the other date then you have a different story.
If they are both string format then you can use the format function within the SQL statement.

[cobs]

See the problem is that the times dont match up

In both tables date and time are in DATE/TIME data-type

Ex

T1
SITE_ID, DATE, TIME, FLOW
1,1/1/01,12:01,0.2
1,1/1/01,13:07,0.3
1,1/1/01,14:25,0.25
1,1/1/01,15:02,0.2
1,1/1/01,16:20,3.5
1,1/1/01,17:37,4.2

T2
SITE_ID, DATE, TIME, P
1,1/1/01,14:37, 0.002
1,1/1/01,17:50,0.2,0.031

REQUIRED RESULTS FROM QUERY
SITE_ID, DATE, TIME, FLOW, P
1,1/1/01,12:01,0.2,0.002
1,1/1/01,13:07,0.3,0.002
1,1/1/01,14:25,0.25,0.002
1,1/1/01,15:02,0.2,0.031
1,1/1/01,16:20,3.5,0.031
1,1/1/01,17:37,4.2,0.031

[iDxMan]

Sounds like a fine mess.


REQUIRED RESULTS FROM QUERY
SITE_ID, DATE, TIME, FLOW, P
1,1/1/01,12:01,0.2,0.002
1,1/1/01,13:07,0.3,0.002
1,1/1/01,14:25,0.25,0.002
1,1/1/01,15:02,0.2,0.031
1,1/1/01,16:20,3.5,0.031
1,1/1/01,17:37,4.2,0.031


So how are you matching anything at all? Nothing from either tables tells me that the 0.002 value should go with that row from T1.

-r

[DNAunion2000]

DNAunion2000: It looks to me like he's trying to find the closest time that is less than or equal to the time from the other table.

[cobs]

Thats exactly it, If the date of P is equal to or less then the date of Flow, then that value of P will be appended onto the QUERY table until a new value of P appears....

[DNAunion2000]

cobs: Thats exactly it, If the date of P is equal to or less then the date of Flow, then that value of P will be appended onto the QUERY table until a new value of P appears....

DNAunion2000: I don't want to say that it's impossible with SQL, but it is something that just screams record-oriented (instead of set-oriented) processing to me. That is, I would do a table scan -- a top-to-bottom, one-record-at-a-time loop -- on one table and then use a function to calculate which record is the best match from the other table based on the nearness of time.

[santosh]

table T2 seems to have an extra value in the second record..( 0.2) where do U fit this in?

[DNAunion2000]

/*DNAunion*/ I have never actually used one in real life, but wouldn't a correlated subquery handle this? I had mentioned that it looks like a job for a table scan, and, in a sense, isn't that what a correlated subquery does on the main table, in the outer SELECT?