Sum of projects within distance D before sales date T

  • Thread starter Thread starter whrogers
  • Start date Start date
W

whrogers

I have a table of sales including location (xy coords) and date of sale. I
have another table of projects including location (xy coords) of date of
project. I'd like to sum the number of projects that exited before a sale
within a given distance (euclidean distance must be calcuated) for every
sale.

Any suggestions?

Thank you
 
Does your projects table contain a date field as well?

Can you give an example of a couple of rows from each table, and what you
expect the results to look like? It might help me understand exactly what
you are trying to do.
 
SELECT sales.saleID, LAST(sales.dateStamp), COUNT(projects.dateStamp)
FROM sales LEFT JOIN projects
ON sales.dateStamp >= projects.dateStamp
AND sales.x >= projects.x - dist
AND sales.x <= projects.x + dist
AND sales.y >= projects.y - dist
AND sales.y <= projects.x + dist
AND (sales.x-projects.x)^2 + (sales.y-projects.y)^2 <= dist ^2
GROUP BY sales.saleID


where dist is the maximum distance we are allowed to use.

Note that we can use:

ON sales.dateStamp >= projects.dateStamp
AND (sales.x-projects.x)^2 + (sales.y-projects.y)^2 <= dist ^2

but it may be noticeably faster if we eliminate the Euclidian computation
for as many records as it is possible. Indeed, if

sales.x < projects.x - dist


then the Euclidian distance will exceed the maximum distance we are allowed.



We used an outer join, since we assume it is possible that no projects
satisfy all the criteria, and, in such case, COUNT(projects.dateStamp)
returns zero, as we want.


Hoping it may help,
Vanderghast, Access MVP
 
Michel Walsh said:
SELECT sales.saleID, LAST(sales.dateStamp), COUNT(projects.dateStamp)
FROM sales LEFT JOIN projects
ON sales.dateStamp >= projects.dateStamp
AND sales.x >= projects.x - dist
AND sales.x <= projects.x + dist
AND sales.y >= projects.y - dist
AND sales.y <= projects.x + dist
AND (sales.x-projects.x)^2 + (sales.y-projects.y)^2 <= dist ^2
GROUP BY sales.saleID


where dist is the maximum distance we are allowed to use.

Note that we can use:

ON sales.dateStamp >= projects.dateStamp
AND (sales.x-projects.x)^2 + (sales.y-projects.y)^2 <= dist ^2

but it may be noticeably faster if we eliminate the Euclidian computation
for as many records as it is possible. Indeed, if

sales.x < projects.x - dist


then the Euclidian distance will exceed the maximum distance we are allowed.



We used an outer join, since we assume it is possible that no projects
satisfy all the criteria, and, in such case, COUNT(projects.dateStamp)
returns zero, as we want.


Hoping it may help,
Vanderghast, Access MVP

I just tested it on a sample database and it worked great.
Thank you!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top