Active Foster Homes at Date by Species
###
Movements
###
33715/Any rev03
###
The number of active foster homes on a given date by species, including capacity count
###
en
###
$VAR date DATE Active on$
SELECT
SpeciesName,
COUNT(DISTINCT m.OwnerID) AS TotalHomes,
SUM(o.FosterCapacity) AS TotalCapacity
FROM adoption m
INNER JOIN animal a ON a.ID = m.AnimalID
INNER JOIN species s ON s.ID = a.SpeciesID
INNER JOIN owner o ON o.ID = m.OwnerID
WHERE m.MovementType = 2
AND m.MovementDate <= '$@date$' AND (m.ReturnDate Is NULL or m.ReturnDate > '$@date$')
AND (a.DeceasedDate Is Null OR a.DeceasedDate > '$@date$')
GROUP BY SpeciesName
###
$$HEADER
<table>
<thead>
<tr>
<th>Species</th>
<th>Active Foster Homes</th>
<th>Capacity of Homes</th>
</tr>
</thead>
<tbody>
HEADER$$

$$BODY
<tr>
<td>$SpeciesName</td>
<td>$TotalHomes</td>
<td>$TotalCapacity</td>
</tr>
BODY$$

$$FOOTER
</tbody>
</table>
<p>Total active foster homes: <b>{SUM.TotalHomes.0}</b></p>
FOOTER$$

