Tricky SQL query - MINUS SETS 1

[VBAHole22]

I have a program that takes a point and creates circles of different sizes around the point. Then these circles are intersected with the locations of hospitals. I then get the number of the hospital out of that intersection.

Anyway here is the issue. I have a table that contains the hospital name and the distance of the circle that it was found in. The trouble is that the set is cumulative so if there was a hopital within 1 mile of the point then it gets in that set. If there is another hospital 3 miles from the point it gets that hospital AND the one that is 1 mile away. So when I get to say 100 miles away I have a lot of hospitals. But If I go 101 miles away I might only get 1 more hospital. I would like to have a query that lists the circle distance and tells me if there are any NEW hospitals in that circle that weren't present in all of the circles inside of it.

I can get this done one at a time like this:

select ring_id, Hosp from results where ring_id = 6
AND Hosp NOT IN
(select Hosp from results where ring_id = 5)

This compares 2 rings that are next to each other. This works great and tells me how many hospitals are in 6 only.

My problem is that I want to do this for ALL not just one at a time. Must I create a cursor and loop or can this be done as a SET?

 

[gmmastros]

If I understand correctly, this results table has multiple records per hospital.

So Hospital 1 could have 3 records
Hosp Ring_id
1 1
1 2
1 3

If my understanding is correct, then you could simply use the max function

Select Max(ring_id) As ring_id, Hosp
From Results

Does this work for you?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[amrita418]

I do not have any sample data to test my code.
Try this:

select ring_id, hosp
from results a
where hosp not in (select hosp from results b where b.ring_id = a.ring_id - 1)

Regards,
AA

 

[vongrunt]

Will this do the trick?

select min(ring_id) as innermost_ring_id, Hosp 
from results 
group by Hosp
having min(ring_id) = 6

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gmmastros]

I'm missing a group by.

Select Max(ring_id) As ring_id, Hosp
From   Results
Group By Hosp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Max or min?

Btw my HAVING() is not necessary

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gmmastros]

I understood it to mean he wanted the max ring_id. based on the example code, he wanted hospitals with ring_id = 6, but not = 5. So max would be correct, wouldn't it?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[amrita418]

I am not vongrunt, but min(ring_id) is where the hosp appears first.

So in this case 6 is where it appears (i.e. not in 5). It could appear in all ring_ids higher than that so max(ring_id) could return 9.

Regards,
AA

 

[vongrunt]

He said "I have a table that contains the hospital name and the distance of the circle that it was found in."

And also "...hospitals in that circle that weren't present in all of the circles inside of it".

So assuming that ring_id represents distance I'd say min() is the way to go.


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[VBAHole22]

You got it rita!

Thank you so much for helping me out. Your sql works perfectly. It provides the ring and hospital for each new hospital that appears in the ring but that was not in the ring before it.

 

[gmmastros]

ok, ok. I get it. I humbly admit defeat.

If this were my app, I would simply have calculated the actual distance from any given point to each hospital based on latitude and longitude. If VBAHole22 is interested, I would be happy to share that algorithm.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[VBAHole22]

I have the capability to calculate the actual distance to the hospital - I am doing this in ArcMap (ESRI).
But this is more of a theoretical exercise in impact analysis. You drop a point that simulates an event (such as a gas leak). And then you radiate impact circles out from the point and assess what can be affect and what resources are nearby to respond.