Animals Returned within 30 days by Species
###
Returns
###
PostgreSQL rev03
###
Animals returned within 30 days where the return was processed between a date range, grouped by species.
###
en
###
SELECT a.AnimalName, a.ID AS AID, a.ShelterCode, t.AnimalType, s.SpeciesName, i.LocationName,
ad.MovementDate, ad.ReturnDate, ad.ReasonForReturn, rr.ReasonName,
EXTRACT(DAY FROM ad.ReturnDate - ad.MovementDate)::integer AS DaysAdopted
FROM adoption ad
INNER JOIN animal a ON a.ID = ad.AnimalID
INNER JOIN animaltype t ON t.ID = a.AnimalTypeID
INNER JOIN species s ON s.ID = a.SpeciesID
INNER JOIN internallocation i ON i.ID = a.ShelterLocation
INNER JOIN entryreason rr ON rr.ID = ad.ReturnedReasonID
WHERE ad.MovementType = 1
AND ad.ReturnDate >= '$ASK DATE Animals returned between$' 
AND ad.ReturnDate <= '$ASK DATE and$'
AND EXTRACT(DAY FROM ad.ReturnDate - ad.MovementDate)::integer < 30
ORDER BY s.SpeciesName, ad.ReturnDate, a.AnimalName
###
$$HEADER
HEADER$$

$$GROUP_SpeciesName
$$HEAD
<h2>$SpeciesName</h2>
<table border="1">
<thead>
<tr>
<th>Name</th>
<th>Code</th>
<th>Type</th>
<th>Location</th>
<th>Adopted</th>
<th>Returned</th>
<th>Days</th>
<th>Reason</th>
<th>Category</th>
</tr>
</thead>
<tbody>
$$FOOT
<tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>Avg: {AVG.DaysAdopted.2}</td>
<td></td>
<td></td>
</tr>
</tbody>
</table>
<p>Total $SPECIESNAME: {COUNT.SpeciesName}</p>
GROUP$$

$$BODY
<tr>
<td><a target="_blank" href="animal?id=$AID">$ANIMALNAME</a></td>
<td>$SHELTERCODE</td>
<td>$ANIMALTYPE</td>
<td>$LOCATIONNAME</td>
<td>$MOVEMENTDATE</td>
<td>$RETURNDATE</td>
<td>$DAYSADOPTED</td>
<td>$REASONFORRETURN</td>
<td>$REASONNAME</td>
</tr>
BODY$$

$$FOOTER
FOOTER$$

