Sum of projects within distance D before sales date T

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
 
D

Dale Fye

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.
 
M

Michel Walsh

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
 
W

whrogers

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
Click to expand...

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