Intakes by Date with Ages and Outcomes
###
Auditing
###
2870/PostgreSQL rev05
###
Animals entering between two dates with their age at intake and final outcomes
###
en
###
SELECT
a.AnimalName, a.ID AS AID, a.ShelterCode, a.DateOfBirth, s.SpeciesName, atp.AnimalType, 
a.BreedName, e.ReasonName, o.OwnerName, a.DateBroughtIn, m.MovementDate,
a.NeuteredDate, a.IdentichipNumber, oo.OwnerName AS OriginalOwner, oo.ID AS OID,
CASE WHEN a.DeceasedDate Is Not Null AND PutToSleep = 0 AND IsDOA = 0 THEN 'Died' 
WHEN a.DeceasedDate Is Not Null AND IsDOA = 1 THEN 'DOA' 
WHEN a.DeceasedDate Is Not Null AND PutToSleep = 1 THEN 'Euthanasia' 
WHEN a.ActiveMovementDate Is Not Null THEN (SELECT MovementType FROM lksmovementtype WHERE ID = a.ActiveMovementType) 
ELSE 'On Shelter' END AS OutcomeName,
CASE WHEN a.DeceasedDate Is Not Null THEN a.DeceasedDate 
WHEN a.ActiveMovementDate Is Not Null THEN a.ActiveMovementDate 
ELSE Null END AS OutcomeDate,
CASE WHEN a.DeceasedDate Is Not Null THEN (SELECT ReasonName FROM deathreason WHERE ID = a.PTSReasonID)
WHEN a.ActiveMovementDate Is Not Null THEN o.OwnerName
ELSE '' END AS OutcomeQualifier,
age(DateBroughtIn, a.DateOfBirth)::varchar AS AgeAtIntake,
CASE WHEN a.DeceasedDate Is Not Null THEN age(DeceasedDate, a.DateOfBirth)::varchar 
WHEN a.ActiveMovementDate Is Not Null THEN age(ActiveMovementDate, a.DateOfBirth)::varchar
ELSE '' END AS AgeAtOutcome
FROM animal a
INNER JOIN species s ON a.SpeciesID = s.ID
INNER JOIN animaltype atp ON a.AnimalTypeID = atp.ID
LEFT OUTER JOIN adoption m ON m.ID = a.ActiveMovementID
LEFT OUTER JOIN owner o ON m.OwnerID = o.ID 
LEFT OUTER JOIN owner oo ON a.OriginalOwnerID = oo.ID
INNER JOIN entryreason e ON a.EntryReasonID = e.ID 
WHERE a.DateBroughtIn >= '$ASK DATE Enter from date$' AND a.DateBroughtIn <= '$ASK DATE Enter to date$ 23:59:59' 
AND NonShelterAnimal = 0 
ORDER BY a.DateBroughtIn
###
$$HEADER
<table border="1">
<thead>
<tr>
<th>Name</th>
<th>Code</th>
<th>Type</th>
<th>Microchip</th>
<th>DOB</th>
<th>Age at Intake</th>
<th>Entry</th>
<th>Altered</th>
<th>Outcome</th>
<th>Age at Outcome</th>
</tr>
</thead>
<tbody>
HEADER$$

$$BODY
<tr>
<td><a target="_blank" href="animal?id=$AID">$ANIMALNAME</a></td>
<td>$SHELTERCODE</td>
<td>$ANIMALTYPE / $BREEDNAME / $SPECIESNAME</td>
<td>$IDENTICHIPNUMBER</td>
<td>$DATEOFBIRTH</td>
<td>$AGEATINTAKE</td>
<td>$REASONNAME : <a target="_blank" href="person?id=$OID">$ORIGINALOWNER</a> $DATEBROUGHTIN</td>
<td>$NEUTEREDDATE</td>
<td>$OUTCOMENAME : $OUTCOMEQUALIFIER $OUTCOMEDATE</td>
<td>$AGEATOUTCOME</td>
</tr>
BODY$$

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