...efficient method of finding closest date between unrelated tables

  • Thread starter Thread starter Need2Know
  • Start date Start date
N

Need2Know

Good Morning All, Is there a more efficient method of finding closest
date between unrelated tables? Any assistance provided is greatly
appreciated. Thank you all in advance.

SELECT TOP 1 tbl1.[SomeDte], tbl2.[SomeOthDte]
FROM tbl1, tbl2
WHERE tbl1.[SomeDte] < tbl2.[SomeOthDte] And tbl2.[SomeOthDte]
Between tbl2.[SomeOthDte] And tbl1.[SomeDte]
ORDER BY tbl1.[SomeDte] DESC;


tbl1 tbl2

SomeDte SomeOthDte
IgnoreDte Field1
Field2
Field3
 
Need2Know said:
Good Morning All, Is there a more efficient method of finding closest
date between unrelated tables? Any assistance provided is greatly
appreciated. Thank you all in advance.

SELECT TOP 1 tbl1.[SomeDte], tbl2.[SomeOthDte]
FROM tbl1, tbl2
WHERE tbl1.[SomeDte] < tbl2.[SomeOthDte] And tbl2.[SomeOthDte]
Between tbl2.[SomeOthDte] And tbl1.[SomeDte]
ORDER BY tbl1.[SomeDte] DESC;


tbl1 tbl2

SomeDte SomeOthDte
IgnoreDte Field1
Field2
Field3
Click to expand...


How do you define "closest"? Until you can specify that,
all we can do is guess what you want.

Your query does not appear to help explain much of anything,
because I can't figure out what it is trying to do. It
seems to be nonsense to me because it just selects the
latest date in tbl1 that is less than any date in tbl2,
which would be a strange definition of "closest".

Note that the Between condition in the Where clause will
always be True (unless tbl2.SomeOthDte is Null).

Your example is too vague to help me clarify whatever you
are trying to say.

After we understand what you want the query to do, then
maybe(?) we can look at its efficiency.
 
Need2Know said:
Good Morning All, Is there a more efficient method of finding closest
date between unrelated tables? Any assistance provided is greatly
appreciated.  Thank you all in advance.
Click to expand...
   SELECT TOP 1 tbl1.[SomeDte], tbl2.[SomeOthDte]
   FROM tbl1, tbl2
   WHERE tbl1.[SomeDte] <  tbl2.[SomeOthDte] And  tbl2.[SomeOthDte]
Between  tbl2.[SomeOthDte] And  tbl1.[SomeDte]
   ORDER BY tbl1.[SomeDte] DESC;
Click to expand...
tbl1                tbl2
Click to expand...
SomeDte             SomeOthDte
IgnoreDte           Field1
           Field2
           Field3
Click to expand...

How do you define "closest"?  Until you can specify that,
all we can do is guess what you want.

Your query does not appear to help explain much of anything,
because I can't figure out what it is trying to do.  It
seems to be nonsense to me because it just selects the
latest date in tbl1 that is less than any date in tbl2,
which would be a strange definition of "closest".

Note that the Between condition in the Where clause will
always be True (unless tbl2.SomeOthDte is Null).

Your example is too vague to help me clarify whatever you
are trying to say.

After we understand what you want the query to do, then
maybe(?) we can look at its efficiency.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

My apologies... and thank you for responding. To clarify, I'm trying
to compare the date columns between tbl1 and tbl2 to find the closest
matching date in tbl1 then display the resulting date in the CloseDate
field.

tbl1 tbl2 CloseDate
12/31/2008 8/1/2009 7/31/2009
1/31/2009 8/12/2009 7/31/2009
2/28/2009 8/5/2009 7/31/2009
3/31/2009 8/10/2009 7/31/2009
4/30/2009 4/25/2009 3/31/2009
5/31/2009
6/30/2009
7/31/2009

* tbl1 consists only of dates (all entries are unique)
* tbl2 consists of activity detail in addition to dates (some dates
are repeated).
 
TRy this --
SELECT TOP 1 [TBL1].[Date close], Min(Abs(DateDiff("d",[TBL1].[Date
close],[TBL2].[Date close]))) AS Expr1, [TBL2].[Date close]
FROM [TBL2], [TBL1]
GROUP BY [TBL1].[Date close], [TBL2].[Date close]
ORDER BY [TBL1].[Date close] DESC , Min(Abs(DateDiff("d",[TBL1].[Date
close],[TBL2].[Date close])));
 
Need2Know said:
Need2Know said:
Good Morning All, Is there a more efficient method of finding closest
date between unrelated tables? Any assistance provided is greatly
appreciated.  Thank you all in advance.
Click to expand...
   SELECT TOP 1 tbl1.[SomeDte], tbl2.[SomeOthDte]
   FROM tbl1, tbl2
   WHERE tbl1.[SomeDte] <  tbl2.[SomeOthDte] And  tbl2.[SomeOthDte]
Between  tbl2.[SomeOthDte] And  tbl1.[SomeDte]
   ORDER BY tbl1.[SomeDte] DESC;
Click to expand...
tbl1                tbl2
Click to expand...
SomeDte             SomeOthDte
IgnoreDte           Field1
           Field2
           Field3
Click to expand...

How do you define "closest"?  Until you can specify that,
all we can do is guess what you want.

Your query does not appear to help explain much of anything,
because I can't figure out what it is trying to do.  It
seems to be nonsense to me because it just selects the
latest date in tbl1 that is less than any date in tbl2,
which would be a strange definition of "closest".

Note that the Between condition in the Where clause will
always be True (unless tbl2.SomeOthDte is Null).

Your example is too vague to help me clarify whatever you
are trying to say.

After we understand what you want the query to do, then
maybe(?) we can look at its efficiency.
Click to expand...

My apologies... and thank you for responding. To clarify, I'm trying
to compare the date columns between tbl1 and tbl2 to find the closest
matching date in tbl1 then display the resulting date in the CloseDate
field.

tbl1 tbl2 CloseDate
12/31/2008 8/1/2009 7/31/2009
1/31/2009 8/12/2009 7/31/2009
2/28/2009 8/5/2009 7/31/2009
3/31/2009 8/10/2009 7/31/2009
4/30/2009 4/25/2009 3/31/2009
5/31/2009
6/30/2009
7/31/2009

* tbl1 consists only of dates (all entries are unique)
* tbl2 consists of activity detail in addition to dates (some dates
are repeated).
Click to expand...


This example seems to imply that the "closest" date is the
latest date in tbl1 that is before the tabl2 record's date.

In that case, I think you should try:

SELECT tbl2.*, (SELECT Max(tbl1.datefield)
FROM tbl1
WHERE tbl1.datefield < tbl2.datefield
) As ClosestDate
FROM tbl2
 
Need2Know said:
Need2Know wrote:
Good Morning All, Is there a more efficient method of finding closest
date between unrelated tables? Any assistance provided is greatly
appreciated.  Thank you all in advance.
   SELECT TOP 1 tbl1.[SomeDte], tbl2.[SomeOthDte]
   FROM tbl1, tbl2
   WHERE tbl1.[SomeDte] <  tbl2.[SomeOthDte] And  tbl2.[SomeOthDte]
Between  tbl2.[SomeOthDte] And  tbl1.[SomeDte]
   ORDER BY tbl1.[SomeDte] DESC;
tbl1                tbl2
SomeDte             SomeOthDte
IgnoreDte           Field1
           Field2
           Field3
How do you define "closest"?  Until you can specify that,
all we can do is guess what you want.
Your query does not appear to help explain much of anything,
because I can't figure out what it is trying to do.  It
seems to be nonsense to me because it just selects the
latest date in tbl1 that is less than any date in tbl2,
which would be a strange definition of "closest".
Note that the Between condition in the Where clause will
always be True (unless tbl2.SomeOthDte is Null).
Your example is too vague to help me clarify whatever you
are trying to say.
After we understand what you want the query to do, then
maybe(?) we can look at its efficiency.
Click to expand...
Click to expand...
My apologies... and thank you for responding. To clarify, I'm trying
to compare the date columns between tbl1 and tbl2 to find the closest
matching date in tbl1 then display the resulting date in the CloseDate
field.
Click to expand...
tbl1                tbl2            CloseDate
12/31/2008  8/1/2009                7/31/2009
1/31/2009           8/12/2009       7/31/2009
2/28/2009           8/5/2009                7/31/2009
3/31/2009           8/10/2009               7/31/2009
4/30/2009           4/25/2009               3/31/2009
5/31/2009
6/30/2009
7/31/2009
Click to expand...
* tbl1 consists only of dates (all entries are unique)
* tbl2 consists of activity detail in addition to dates (some dates
are repeated).
Click to expand...

This example seems to imply that the "closest" date is the
latest date in tbl1 that is before the tabl2 record's date.

In that case, I think you should try:

SELECT tbl2.*, (SELECT Max(tbl1.datefield)
                                                                FROM tbl1
                                                                WHERE tbl1.datefield < tbl2.datefield
                                                                ) As ClosestDate
FROM tbl2

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you Marsh! This is exactly what I was looking for. Again my
apologies for the vague post and my deppest thanks for your time and
patience.
 
TRy this --
SELECT TOP 1 [TBL1].[Date close], Min(Abs(DateDiff("d",[TBL1].[Date
close],[TBL2].[Date close]))) AS Expr1, [TBL2].[Date close]
FROM [TBL2], [TBL1]
GROUP BY [TBL1].[Date close], [TBL2].[Date close]
ORDER BY [TBL1].[Date close] DESC , Min(Abs(DateDiff("d",[TBL1].[Date
close],[TBL2].[Date close])));

--
Build a little, test a little.



Need2Know said:
Good Morning All, Is there a more efficient method of finding closest
date between unrelated tables? Any assistance provided is greatly
appreciated.  Thank you all in advance.
Click to expand...
   SELECT TOP 1 tbl1.[SomeDte], tbl2.[SomeOthDte]
   FROM tbl1, tbl2
   WHERE tbl1.[SomeDte] <  tbl2.[SomeOthDte] And  tbl2.[SomeOthDte]
Between  tbl2.[SomeOthDte] And  tbl1.[SomeDte]
   ORDER BY tbl1.[SomeDte] DESC;
Click to expand...
tbl1               tbl2
Click to expand...
SomeDte            SomeOthDte
IgnoreDte          Field1
           Field2
           Field3
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thank you for the response Karl. Marsh's post was exactly what I was
looking for.
 
Need2Know said:
Need2Know wrote:
Good Morning All, Is there a more efficient method of finding closest
date between unrelated tables? Any assistance provided is greatly
appreciated.  Thank you all in advance.
   SELECT TOP 1 tbl1.[SomeDte], tbl2.[SomeOthDte]
   FROM tbl1, tbl2
   WHERE tbl1.[SomeDte] <  tbl2.[SomeOthDte] And  tbl2.[SomeOthDte]
Between  tbl2.[SomeOthDte] And  tbl1.[SomeDte]
   ORDER BY tbl1.[SomeDte] DESC;
tbl1                tbl2
SomeDte             SomeOthDte
IgnoreDte           Field1
           Field2
           Field3
How do you define "closest"?  Until you can specify that,
all we can do is guess what you want.
Your query does not appear to help explain much of anything,
because I can't figure out what it is trying to do.  It
seems to be nonsense to me because it just selects the
latest date in tbl1 that is less than any date in tbl2,
which would be a strange definition of "closest".
Note that the Between condition in the Where clause will
always be True (unless tbl2.SomeOthDte is Null).
Your example is too vague to help me clarify whatever you
are trying to say.
After we understand what you want the query to do, then
maybe(?) we can look at its efficiency.
My apologies... and thank you for responding. To clarify, I'm trying
to compare the date columns between tbl1 and tbl2 to find the closest
matching date in tbl1 then display the resulting date in the CloseDate
field.
tbl1                tbl2            CloseDate
12/31/2008  8/1/2009                7/31/2009
1/31/2009           8/12/2009       7/31/2009
2/28/2009           8/5/2009                7/31/2009
3/31/2009           8/10/2009               7/31/2009
4/30/2009           4/25/2009               3/31/2009
5/31/2009
6/30/2009
7/31/2009
* tbl1 consists only of dates (all entries are unique)
* tbl2 consists of activity detail in addition to dates (some dates
are repeated).
Click to expand...
Click to expand...
This example seems to imply that the "closest" date is the
latest date in tbl1 that is before the tabl2 record's date.
Click to expand...
In that case, I think you should try:
Click to expand...
SELECT tbl2.*, (SELECT Max(tbl1.datefield)
                                                               FROM tbl1
                                                               WHERE tbl1.datefield < tbl2.datefield
                                                               ) As ClosestDate
FROM tbl2
Click to expand...
- Show quoted text -
Click to expand...

Thank you Marsh!  This is exactly what I was looking for. Again my
apologies for the vague post and my deppest thanks for your time and
patience.- Hide quoted text -

- Show quoted text -
Click to expand...

One more question Marsh,

If I now wanted to check against a third table could I apply the same
logic?

SELECT tbl2.*, (SELECT Max(tbl1.datefield)
FROM tbl1
WHERE tbl1.datefield < tbl2.datefield
) As ClosestDate,(SELECT max(tbl3.[datefield])
FROM tbl3
WHERE tbl3.[datefield] between tbl2.[datefield] and ClosestDate
) As XtraDte
FROM tbl2;

Thanks again.
 
Need2Know said:
If I now wanted to check against a third table could I apply the same
logic?

SELECT tbl2.*, (SELECT Max(tbl1.datefield)
FROM tbl1
WHERE tbl1.datefield < tbl2.datefield
) As ClosestDate,(SELECT max(tbl3.[datefield])
FROM tbl3
WHERE tbl3.[datefield] between tbl2.[datefield] and ClosestDate
) As XtraDte
FROM tbl2;
Click to expand...


Depending on what you mean by "check against a third table",
it might do what you want. Try it and see what you get.

If you want to do the same thing with tbl3 that you did with
tbl1, then it would be:

SELECT tbl2.*, (SELECT Max(tbl1.datefield)
FROM tbl1
WHERE tbl1.datefield < tbl2.datefield
) As ClosestDate,
(SELECT Max(tbl3.datefield)
FROM tbl3
WHERE tbl3.datefield < tbl2.datefield
) As XtraDte
FROM tbl2
 
Back
Top