March 11, 2009 at 5:15 am
Ps: If you don't care about the background info, just look at the code...
I have one table with buildings, a second table with hazards and a third table with elements at risk (buildings exposed to hazards).
Any building can be exposed to 0, 1 or more hazards.
For each building I have the number of occupants. For each hazard I have an event frequency (how often does this hazard occur) and a probability of death given the occurence of an event.
The expected number of lost lives per year for a building is thus:
[frequency]*[probability of death]*[number of occupants in building]
I want to report the "total number of people at risk" together with the "total expected number of lost lives per year", but I run into a problem as the first property should be calculated from buildings that intersect with one or more hazards while the latter must be calculated from all the building-hazard intersections. This will become clearer if you look at the code...
--Create tables
CREATE TABLE #buildings (buildingID INT
, occupants INT)
GO
CREATE TABLE #hazards (hazardID INT
, frequency FLOAT
, prob_of_death FLOAT)
GO
CREATE TABLE #elements_at_risk (buildingID INT
,hazardID INT)
GO
--Populate building table with three buildings having 2 occupants each
INSERT INTO #buildings
SELECT 1, 2 --Building 1 has 2 occupants
UNION ALL
SELECT 2, 2 --Building 2 has 2 occupants
UNION ALL
SELECT 3, 2 --Building 3 has 2 occupants
GO
--Populate hazards table with two types of hazards
INSERT INTO #hazards
SELECT 1, 1.0/100, 0.5 --Hazard 1 occcurs once every 100 years and has a 50% chance of killing you
UNION ALL
SELECT 2, 1.0/50, 0.3 --Hazard 2 occcurs once every 50 years and has a 30% chance of killing you
GO
/*
Populate elements_at_risk table:
Building 1 is exposed to booth hazard 1 and 2
Building 2 is exposed to booth hazard 1
Building 3 is not exposed to any hazard
*/
INSERT INTO #elements_at_risk
SELECT 1, 1
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 2, 2
GO
--Select all elements at risk and join the building and hazard tables
SELECT b.buildingID
, b.occupants
, h.hazardID
, h.frequency
, h.prob_of_death
, h.frequency*h.prob_of_death*b.occupants AS expected_number_of_deaths_per_year_in_building
FROM #elements_at_risk e
INNER JOIN #buildings b ON e.buildingID=b.buildingID
INNER JOIN #hazards h ON e.hazardID=h.hazardID
/*
The total number of affected people for or all hazards is 4
(two occupants from bld 1 and two occupants from bld 2)
The total risk to life (expected number of deaths per year) is 0.034
(the sum of all the annual probabilities)
*/
/*
The following query returns the correct risk to life but the wrong number of affected people
because occupants affected by multiple hazards should only be counted once (only cats have 9 lives)
*/
SELECT SUM(b.occupants) AS 'People at risk (incorrect)'
, SUM(h.frequency*h.prob_of_death*b.occupants) AS 'Risk to life (correct)'
FROM #elements_at_risk e
INNER JOIN #buildings b ON e.buildingID=b.buildingID
INNER JOIN #hazards h ON e.hazardID=h.hazardID
/*
The correct number of people at risk can be found by e.g. using a subquery,
but now I can't calculate the risk to life
*/
SELECT SUM(b.occupants) AS 'Correct number of people at risk'
FROM #buildings b
WHERE EXISTS (SELECT * FROM #elements_at_risk e WHERE e.buildingID=b.buildingID)
/*
How can I query both these two sums in the same query?
*/
DROP TABLE #buildings
DROP TABLE #hazards
DROP TABLE #elements_at_risk
GO
I can of course calculate "people at risk" and "risk to life" in two different queries that later join, but I was wondering if anyone had a better solution.
March 11, 2009 at 7:41 am
This works, but it is basically the same as doing it in a join later.
SELECT
SUM(b.occupants) AS 'People at risk (incorrect)',
A.atrisk,
SUM(h.frequency * h.prob_of_death * b.occupants) AS 'Risk to life (correct)'
FROM
#elements_at_risk e INNER JOIN
#buildings b
ON e.buildingID = b.buildingID INNER JOIN
#hazards h
ON e.hazardID = h.hazardID CROSS Join
(SELECT
SUM(b.occupants) AS atrisk
FROM
#buildings b
WHERE
EXISTS ( SELECT
*
FROM
#elements_at_risk e
WHERE
e.buildingID = b.buildingID )) AS A
GROUP BY
A.atrisk
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply