The below SQL returns unexpected results - SQL, table and result samples
provided
The objective: return rows from the master table when a transaction exist
for
the transaction date less 1 month
SELECT a.PKey, a.[SDate], a.[EDate], DateAdd('m',-1,b.TDate) AS TDless1
FROM tblMaster AS a
, tblTran AS b
WHERE (A.PKey=B.PKey)
AND (DateAdd("M",-1,B.TDate) Between A.SDate and A.EDate);
tblMASTER
-----------------
PKey SDate EDate
00001 4/1/2008 5/1/2008
00001 5/1/2008 6/1/2008
00001 6/1/2008 6/1/2008
00001 7/1/2008 7/1/2008
tblTran
----------
PKey TDate
00001 6/1/2008
Results
----------
PKey SDate EDate
00001 5/1/2008 6/1/2008 **** not expected **** any ideas?
00001 6/1/2008 6/1/2008 **** expected and ok
|
