Query to find nearest coming date on Table2 (End of range) from date on Table 1 (event date)

[Mattbro]

Hi all. I have a db with two tables:



TbTest which has [TestNumber], [TestDate], [TestMethod] and PK-a concatenated filed of [TestNumber] and [Test Method] with serves as the PK.

TbDateRange which gives the [TestMethod], the date range of the parts used-[DateStart] and [DateEnd], and the relevant serial number of the test equipment [BatchNo].

I am trying to write a query that will give me the batch number of the part used for each test in TbTest, e.g, [TestNumber],[BatchNo], based upon the [TestDate] and [DateEnd].

SELECT TbTest.TestNumber, TbTest.TestMethod, TbTest.TestDate, TbDateRange.DateEnd, DateDiff("d",[DateEnd],[TestDate]) AS Expr1FROM TbDateRange, TbTest
WHERE (((TbTest.TestMethod)="ELECTRODE") AND ((DateDiff("d",[DateEnd],[TestDate]))>0))
ORDER BY DateDiff("d",[DateEnd],[TestDate]);

Im failing miserably. If anyone has any advice, it would be appreciated.



Many thanks,
Mattbro451

[madpiet]

Something like this?
You don't *have* to join on an equality... you can use >=,>,<,<=

SELECT TbTest.TestNumber, TbTest.ID, MIN(TbTest.TestDate)
FROM TbTest INNER JOIN TbDateRange ON TbTest.TestMethod = TbDateRange.Method
WHERE (((TbTest.TestDate)>=[DateStart] And (TbTest.TestDate)<=[DateEnd]))
GROUP BY tbTest.TestNumber, tbTest.ID;

[madpiet]

or by "nearest" did you mean MIN(ABS(DATEDIFF(...))?

[Mattbro]

Hi Madpiet-thanks for the input. I've tried your suggestion, but I can't see how to apply it to get the desired results. Your query gives the following;


I need to extend it to give the range from TbDateRange and thus the appropriate batchno. What I am trying to produce is;

If the testdate can be used to show the closest approaching DateEnd, I should be able to find the appropriate batch no used.
Any ideas? K/R, Mattbro

[xps35]

Try

SELECT TbTest.TestNumber, TbTest.ID, TbDateRange.DateStart, TbDateRange.DateEnd, TbDateRange.BatchNoFROM TbDateRange, TbTest
WHERE TbTest.TestMethod=TbDateRange.Method AND 
      TbTest.TestDate>=DateStart And 
      TbTest.TestDate<=DateEnd

[Mattbro]

That is fantastic. Many thanks Peter!