Length of Stay by Species (on shelter between two dates)
###
Statistics
###
33018/Any rev02
###
Shows time on shelter by species for animals who were on the shelter between two dates
###
en
###
$VAR from DATE On shelter between$
$VAR to DATE and$

SELECT
a.AnimalName, a.ShelterCode, a.ID AS AID,
s.SpeciesName, e.ReasonName,
a.DateBroughtIn, a.TimeOnShelter, a.DaysOnShelter, a.AnimalAge, 
CASE WHEN a.DeceasedDate Is Not Null THEN a.DeceasedDate WHEN a.ActiveMovementType = 2 THEN Null ELSE a.ActiveMovementDate END AS DateExit
FROM animal a
INNER JOIN species s ON s.ID = a.SpeciesID
INNER JOIN entryreason e ON e.ID = a.EntryReasonID
WHERE 
NOT EXISTS (SELECT MovementDate FROM adoption WHERE MovementDate <= '$@from$ 23:59:59' AND (ReturnDate Is Null OR ReturnDate > '$@from$') AND MovementType NOT IN (2,8) AND AnimalID = a.ID)
AND DateBroughtIn <= '$@to$ 23:59:59'
AND NonShelterAnimal = 0
AND (DeceasedDate Is Null OR DeceasedDate > '$@from$ 23:59:59')
ORDER BY s.SpeciesName, a.DateBroughtIn
###
$$HEADER
HEADER$$

$$GROUP_SpeciesName
$$HEAD
<h2>$SpeciesName</h2>
<table border="1">
<thead>
<tr>
<th>Code</th>
<th>Name</th>
<th>Age</th>
<th>Entry Category</th>
<th>Entered</th>
<th>Left</th>
<th>Days In Care</th>
</tr>
</thead>
<tbody>
$$FOOT
</tbody>
</table>
<p>Total $SpeciesName: {COUNT.SpeciesName}, Average: {AVG.DaysOnShelter.2} days</p>
GROUP$$

$$BODY
<tr>
<td>$SHELTERCODE</td>
<td><a target="_blank" href="animal?id=$AID">$ANIMALNAME</a></td>
<td>$ANIMALAGE</td>
<td>$REASONNAME</td>
<td>$DATEBROUGHTIN</td>
<td>$DATEEXIT</td>
<td>$DAYSONSHELTER</td>
</tr>
BODY$$

$$FOOTER
FOOTER$$

