By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Facility (
"Id" INTEGER,
"Lat" FLOAT,
"Long" FLOAT,
"Resource1" INTEGER,
"Resource2" INTEGER
);
INSERT INTO Facility
("Id", "Lat", "Long", "Resource1", "Resource2")
VALUES
('1', '50.123', '4.23', '5', '12'),
('2', '61.234', '5.34', '0', '9'),
('3', '50.634', '4.67', '21', '18');
3 rows affected
DECLARE @latQuery float = 50.634,
@LongQuery float = 4.67,
@res1Query int = 10,
@res2Query int = 20;
DECLARE @origin geography = geography::Point(@latQuery, @LongQuery, 4326);
SELECT
f.Id,
f.Lat,
f.Long,
f.Resource1,
f.Resource2
FROM (
SELECT f.*,
SumRes1 = SUM(f.Resource1) OVER (ORDER BY v1.Distance ROWS UNBOUNDED PRECEDING) - f.Resource1,
SumRes2 = SUM(f.Resource2) OVER (ORDER BY v1.Distance ROWS UNBOUNDED PRECEDING) - f.Resource2
FROM Facility f
CROSS APPLY (VALUES(
@origin.STDistance(geography::Point(f.Lat, f.Long, 4326))
)) v1(Distance)
) f
WHERE (
f.SumRes1 < @res1Query
OR f.SumRes2 < @res2Query
);
| Id | Lat | Long | Resource1 | Resource2 |
|---|---|---|---|---|
| 3 | 50.634 | 4.67 | 21 | 18 |
| 1 | 50.123 | 4.23 | 5 | 12 |