Fosters by Date and Age Group with Outcomes
###
Movements
###
Any rev03
###
Foster animals between two dates with their final outcomes, grouped by age group at intake
###
en
###
SELECT
a.AnimalName, a.ID AS AID, a.ShelterCode, a.DateOfBirth, a.AgeGroup,
e.ReasonName, o.OwnerName, o.ID AS OID, m.MovementDate, s.SpeciesName,
a.NeuteredDate,
CASE WHEN a.DeceasedDate Is Not Null THEN 'Died' WHEN a.ActiveMovementDate Is Not Null AND a.ActiveMovementType > 0 THEN mt.MovementType ELSE 'None' END AS OutcomeName,
CASE WHEN a.DeceasedDate Is Not Null THEN a.DeceasedDate WHEN a.ActiveMovementDate Is Not Null AND a.ActiveMovementType = 1 THEN a.ActiveMovementDate ELSE Null END AS OutcomeDate,
no.OwnerName AS OutcomePerson, no.ID AS NOID, no.OwnerAddress AS OutcomeAddress, no.OwnerTown AS OutcomeTown, no.OwnerCounty AS OutcomeCounty,
no.OwnerPostcode AS OutcomePostcode, no.HomeTelephone AS OutcomeHomeTelephone, no.WorkTelephone AS OutcomeWorkTelephone,
no.MobileTelephone AS OutcomeMobileTelephone, no.EmailAddress AS OutcomeEmail
FROM animal a
INNER JOIN adoption m ON m.AnimalID = a.ID
INNER JOIN owner o ON m.OwnerID = o.ID
INNER JOIN entryreason e ON a.EntryReasonID = e.ID
INNER JOIN species s ON a.SpeciesID = s.ID
LEFT OUTER JOIN lksmovementtype mt ON a.ActiveMovementType = mt.ID
LEFT OUTER JOIN adoption ma ON ma.ID = a.ActiveMovementID
LEFT OUTER JOIN owner no ON no.ID = ma.OwnerID
WHERE m.MovementType = 2 AND m.MovementDate >= '$ASK DATE Enter from date$' AND m.MovementDate <= '$ASK DATE Enter to date$'
ORDER BY s.SpeciesName, a.AgeGroup, m.MovementDate
###
$$HEADER
HEADER$$

$$GROUP_SpeciesName
$$HEAD
$$FOOT
GROUP$$

$$GROUP_AgeGroup
$$HEAD
<h2>$AgeGroup $SpeciesName</h2>
<table border="1">
<thead>
<tr>
<th>Animal</th>
<th>Code</th>
<th>DOB</th>
<th>Entry category</th>
<th>Fosterer</th>
<th>Foster Date</th>
<th>Neutered/Spayed</th>
<th>Outcome</th>
<th>Outcome date</th>
<th>Outcome person</th>
</tr>
</thead>
<tbody>
$$FOOT
</tbody>
</table>
<h2>Total $AgeGroup $SpeciesName: {COUNT.AgeGroup}</h2>
GROUP$$

$$BODY
<tr>
<td><a target="_blank" href="animal?id=$AID">$ANIMALNAME</a></td>
<td>$SHELTERCODE</td>
<td>$DATEOFBIRTH</td>
<td>$REASONNAME</td>
<td><a target="_blank" href="person?id=$OID">$OWNERNAME</a></td>
<td>$MOVEMENTDATE</td>
<td>$NEUTEREDDATE</td>
<td>$OUTCOMENAME</td>
<td>$OUTCOMEDATE</td>
<td><b><a target="_blank" href="person?id=$NOID">$OUTCOMEPERSON</a></b><br/>
$OUTCOMEADDRESS<br/>
$OUTCOMETOWN $OUTCOMECOUNTY $OUTCOMEPOSTCODE<br/>
$OUTCOMEHOMETELEPHONE $OUTCOMEWORKTELEPHONE $OUTCOMEMOBILETELEPHONE
<i>$OUTCOMEEMAIL</i></td>
</tr>
BODY$$

$$FOOTER
FOOTER$$
