Your help write a query to calculate Coverage

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Your help write a query to calculate Coverage

Author

Topic

dantesfu
Starting Member

7 Posts

Posted - 2007-09-09 : 15:04:44

I hope someone would help me write this query. I have two tables one showing no. of Health Centers and another table displaying the Population of reach Region/state.

My objective is to calculate the Health Center Coverage per region. Each Health Center is suppose to cover 5,000 people. Therefore, I can get the coverage by taking ((No. of Region Health Center) Multiplied * by 5,000 divided by Region Population.

using the Region Delmbe below as example : (293 * 5,000) / 2931781 = 49.9 percent

I realize I can use a UNION ALL to join the values for reach regions but I can't figure out to write the above formula in a query.

Thank you!!

HealthCenters State

293 Delmbe
413 Awawa
481 Grray

Population State

2931781 Delmbe
4136082 Awawa
4819938 Grray

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-09 : 15:38:42

SELECT t1.HealthCenters * 5000.0 / t2.Population
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.State = t1.State

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources