Outcomes by Species and Entry Category
###
Movements
###
Any rev02
###
Outcome totals for animals entering between two dates, broken down by species and entry category
###
en
###
$VAR from DATE Left between $
$VAR to DATE and $

SELECT
a.ID, sp.SpeciesName, er.ReasonName, mt.MovementType AS OutcomeName
FROM adoption m
INNER JOIN lksmovementtype mt ON mt.ID = m.MovementType
INNER JOIN animal a ON a.ID = m.AnimalID
LEFT OUTER JOIN entryreason er ON er.ID = a.EntryReasonID
LEFT OUTER JOIN basecolour bc ON bc.ID = a.BaseColourID
LEFT OUTER JOIN animaltype tp ON tp.ID = a.AnimalTypeID
LEFT OUTER JOIN species sp ON sp.ID = a.SpeciesID
WHERE m.MovementDate >= '$@from$' AND m.MovementDate <= '$@to$'

UNION SELECT
a.ID, sp.SpeciesName, er.ReasonName,
CASE WHEN PutToSleep = 0 THEN 'Died' ELSE 'Euthanasia' END AS OutcomeName
FROM animal a
LEFT OUTER JOIN entryreason er ON er.ID = a.EntryReasonID
LEFT OUTER JOIN basecolour bc ON bc.ID = a.BaseColourID
LEFT OUTER JOIN animaltype tp ON tp.ID = a.AnimalTypeID
LEFT OUTER JOIN species sp ON sp.ID = a.SpeciesID
WHERE a.DeceasedDate >= '$@from$' AND a.DeceasedDate <= '$@to$' AND a.DiedOffShelter = 0

ORDER BY SpeciesName, ReasonName, OutcomeName
###
$$HEADER
HEADER$$

$$GROUP_SpeciesName
$$HEAD
$$FOOT
<h2>$SpeciesName: {COUNT.SpeciesName}</h2>
GROUP$$

$$GROUP_ReasonName
$$HEAD
$$FOOT
<h3>$SpeciesName $ReasonName: {COUNT.ReasonName}</h3>
GROUP$$

$$GROUP_OutcomeName
$$HEAD
$$FOOT
<p>$SpeciesName $ReasonName $OutcomeName: {COUNT.OutcomeName}</p>
GROUP$$

$$BODY
BODY$$

$$FOOTER
FOOTER$$
