Intakes by Jurisdiction with Outcomes
###
Auditing
###
34407/Any rev04
###
Animals entering between two dates with their final outcomes, broken down by jurisdiction
###
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, 
j.JurisdictionName, 
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
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
LEFT OUTER JOIN jurisdiction j ON a.JurisdictionID = j.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 j.JurisdictionName, a.DateBroughtIn
###
$$HEADER
HEADER$$

$$GROUP_JurisdictionName
$$HEAD
<h2>$JURISDICTIONNAME</h2>
<table border="1">
<thead>
<tr>
<th>Name</th>
<th>Code</th>
<th>Type</th>
<th>Microchip</th>
<th>DOB</th>
<th>Entry</th>
<th>Altered</th>
<th>Outcome</th>
</tr>
</thead>
<tbody>
$$FOOT
</tbody>
</table>
<p>Total $JURISDICTIONNAME: {COUNT.JURISDICTIONNAME}</p>
GROUP$$

$$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>$REASONNAME : <a target="_blank" href="person?id=$OID">$ORIGINALOWNER</a> $DATEBROUGHTIN</td>
<td>$NEUTEREDDATE</td>
<td>$OUTCOMENAME : $OUTCOMEQUALIFIER $OUTCOMEDATE</td>
</tr>
BODY$$

$$FOOTER
FOOTER$$