Nearest matching date value

P

Pam80

I have two tables

T_1
WELL1 DT_1 TEMP MEAS_ID_NUM
A1 3.7.2007 5 1
A1 2.9.2007 4.5 2
A5 15.4.2006 6 3
etc.

T_2
WELL2 DT_2 WTRLVL
A1 3.7.2007 88
A1 30.8.2007 88.5
A1 3.11.2007 88.1
A1 1.12.2007 88.4
A5 20.4.2006 76
A5 7.6.2006 76.3
A5 13.9.2006 75.9

Is it possible to create sql statement where I can find just the nearest
match for wells and dates in table T_1 from table T_2 and join these two
tables:

WELL1 DT_1 DT_2 WTRLVL TEMP
MEAS_ID_NUM
A1 3.7.2007 3.7.2007 88 5
1
A1 2.9.2007 30.8.2007 88.5 4.5
2
A5 15.4.2006 20.4.2006 76 6
3
etc.

All dates are date datatype

I have tried syntax

select t1.well1, to_char(t1.dt_1,'dd.mm.yyyy') dt_1,
to_char(t2.dt_2,'dd.mm.yyyy') dt_2,
t2.wtrlvl,t1.temp,t1.meas_id_num
from t1,t2
where t1.well1 = t2.well2 and
abs(t1.dt_1 - t2.dt_2) = (select minabs((t2inner.dt_1 - t2inner.dt_2))
from t1 t1inner, t2 t2inner
where t1inner.well1 = t2inner.well2 and
t1inner.well1 = t1.well1);

but for some reason it doesn't work and I get:
Syntax error (missing operator) in query expression
'to_char(t1.dt_1'dd.mm.yyyy')dt_1'.

I don't know how to fix this problem
 
J

John Spencer

What is To_Char? Is it a custom function? IF so, what arguments (how many)
does it require? IF this is an Access query and not a pass-through why not
use format?

SELECT t1.well1
, FORMAT(t1.dt_1,"dd.mm.yyyy") AS dt_1
, FORMAT(t2.dt_2,"dd.mm.yyyy") AS dt_2
, t2.wtrlvl
,t1.temp
,t1.meas_id_num
FROM t1 INNER JOIN t2
ON t1.well1 = t2.well2
WHERE abs(t1.dt_1 - t2.dt_2) =
(SELECT min(abs(t2inner.dt_1 - t2inner.dt_2))
FROM t1 t1inner INNER JOIN t2 t2inner
ON t1inner.well1 = t2inner.well2
WHERE t1inner.well1 = t1.well1);



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Top