Average Time On Foster By Date and Species
###
Statistics
###
2721/PostgreSQL rev02
###
Average time spent on foster for all animals by species and date.
###
en
###
SELECT
SpeciesName,
CAST(AVG(CASE WHEN ReturnDate is null THEN EXTRACT(DAY from now() - MovementDate) ELSE EXTRACT(DAY from ReturnDate - MovementDate) END) AS INTEGER) AS avtimeonfoster
FROM adoption
INNER JOIN animal ON adoption.AnimalID = animal.ID
INNER JOIN species ON species.ID = animal.SpeciesID
WHERE MovementType = 2 AND MovementDate > '$ASK DATE Enter the from date$' AND MovementDate < '$ASK DATE Enter the to date$'
GROUP BY SpeciesName
###
$$HEADER
<table border="1">
<thead>
<tr>
<th>Species</th>
<th>Average Days On Foster</th>
</tr>
</thead>
<tbody>
HEADER$$

$$BODY
<tr>
<td>$SPECIESNAME</td>
<td>$AVTIMEONFOSTER</td>
</tr>
BODY$$

$$FOOTER
</tbody>
</table>
FOOTER$$

