ADCH Annual Survey
###
Figures
###
33600/Any rev02
###
Figures needed to complete the annual ADCH member questionnaire.
###
en
###
$VAR year NUMBER Enter the year, eg: 2016$

SELECT

'$@year$' AS year,

(SELECT COUNT(*) FROM animal
 INNER JOIN entryreason ON entryreason.ID = animal.EntryReasonID
 WHERE DateBroughtIn >= '$@year$-01-01' AND DateBroughtIn <= '$@year$-12-31'
 AND SpeciesID = 1 
 AND NonShelterAnimal = 0 AND OriginalOwnerID = 0 AND ReasonName LIKE '%Stray%') AS DogsIntakeStrayPublic,

(SELECT COUNT(*) FROM animal
 WHERE DateBroughtIn >= '$@year$-01-01' AND DateBroughtIn <= '$@year$-12-31'
 AND SpeciesID = 1 
 AND NonShelterAnimal = 0 AND OriginalOwnerID > 0) AS DogsIntakeSurrender,

(SELECT COUNT(*) FROM animal
 INNER JOIN entryreason ON entryreason.ID = animal.EntryReasonID 
 WHERE DateBroughtIn >= '$@year$-01-01' AND DateBroughtIn <= '$@year$-12-31'
 AND SpeciesID = 1 
 AND NonShelterAnimal = 0 AND OriginalOwnerID = 0 
 AND ReasonName NOT LIKE '%Stray%') AS DogsIntakeOther,

(SELECT COUNT(*) FROM animal
 WHERE DateBroughtIn >= '$@year$-01-01' AND DateBroughtIn <= '$@year$-12-31'
 AND SpeciesID = 1 
 AND NonShelterAnimal = 0) AS DogsIntakeTotal,

(SELECT COUNT(*) FROM animal
 INNER JOIN entryreason ON entryreason.ID = animal.EntryReasonID
 WHERE DateBroughtIn >= '$@year$-01-01' AND DateBroughtIn <= '$@year$-12-31'
 AND SpeciesID = 2 
 AND NonShelterAnimal = 0 AND OriginalOwnerID = 0 AND ReasonName LIKE '%Stray%') AS CatsIntakeStrayPublic,

(SELECT COUNT(*) FROM animal
 WHERE DateBroughtIn >= '$@year$-01-01' AND DateBroughtIn <= '$@year$-12-31'
 AND SpeciesID = 2 
 AND NonShelterAnimal = 0 AND OriginalOwnerID > 0) AS CatsIntakeSurrender,

(SELECT COUNT(*) FROM animal
 INNER JOIN entryreason ON entryreason.ID = animal.EntryReasonID 
 WHERE DateBroughtIn >= '$@year$-01-01' AND DateBroughtIn <= '$@year$-12-31'
 AND SpeciesID = 2 
 AND NonShelterAnimal = 0 AND OriginalOwnerID = 0 
 AND ReasonName NOT LIKE '%Stray%') AS CatsIntakeOther,

(SELECT COUNT(*) FROM animal
 WHERE DateBroughtIn >= '$@year$-01-01' AND DateBroughtIn <= '$@year$-12-31'
 AND SpeciesID = 2 
 AND NonShelterAnimal = 0) AS CatsIntakeTotal,


(SELECT COUNT(*) FROM animal
 WHERE DateBroughtIn >= '$@year$-01-01' AND DateBroughtIn <= '$@year$-12-31'
 AND SpeciesID = 1 AND Identichipped = 1 AND IdentichipDate >= DateBroughtIn 
 AND NonShelterAnimal = 0) AS DogsChippedInCare,

(SELECT COUNT(*) FROM animal
 WHERE DateBroughtIn >= '$@year$-01-01' AND DateBroughtIn <= '$@year$-12-31'
 AND SpeciesID = 1 AND Identichipped = 1 AND IdentichipDate Is Null 
 AND NonShelterAnimal = 0) AS DogsAlreadyChipped,

(SELECT COUNT(*) FROM animal
 WHERE DateBroughtIn >= '$@year$-01-01' AND DateBroughtIn <= '$@year$-12-31'
 AND SpeciesID = 1 AND Identichipped = 1 AND (IdentichipDate >= DateBroughtIn OR IdentichipDate Is Null)
 AND NonShelterAnimal = 0) AS DogsChippedTotal,


(SELECT COUNT(*) FROM animal
 WHERE DateBroughtIn >= '$@year$-01-01' AND DateBroughtIn <= '$@year$-12-31'
 AND SpeciesID = 2 AND Identichipped = 1 AND IdentichipDate >= DateBroughtIn 
 AND NonShelterAnimal = 0) AS CatsChippedInCare,

(SELECT COUNT(*) FROM animal
 WHERE DateBroughtIn >= '$@year$-01-01' AND DateBroughtIn <= '$@year$-12-31'
 AND SpeciesID = 2 AND Identichipped = 1 AND IdentichipDate Is Null 
 AND NonShelterAnimal = 0) AS CatsAlreadyChipped,

(SELECT COUNT(*) FROM animal
 WHERE DateBroughtIn >= '$@year$-01-01' AND DateBroughtIn <= '$@year$-12-31'
 AND SpeciesID = 2 AND Identichipped = 1 AND (IdentichipDate >= DateBroughtIn OR IdentichipDate Is Null)
 AND NonShelterAnimal = 0) AS CatsChippedTotal,


(SELECT COUNT(*) FROM animal 
 WHERE DateBroughtIn >= '$@year$-01-01' AND DateBroughtIn <= '$@year$-12-31'
 AND SpeciesID = 1
 AND NonShelterAnimal = 0 AND Neutered = 1 AND NeuteredDate >= DateBroughtIn) AS DogsNeuteredInCare,

(SELECT COUNT(*) FROM animal 
 WHERE NeuteredDate >= '$@year$-01-01' AND NeuteredDate <= '$@year$-12-31'
 AND SpeciesID = 1
 AND NonShelterAnimal = 1 AND Neutered = 1) AS DogsNeuteredOutreach,
 
(SELECT COUNT(*) FROM animal 
 WHERE DateBroughtIn >= '$@year$-01-01' AND DateBroughtIn <= '$@year$-12-31'
 AND SpeciesID = 2
 AND NonShelterAnimal = 0 AND Neutered = 1 AND NeuteredDate >= DateBroughtIn) AS CatsNeuteredInCare,

(SELECT COUNT(*) FROM animal 
 WHERE NeuteredDate >= '$@year$-01-01' AND NeuteredDate <= '$@year$-12-31'
 AND SpeciesID = 2
 AND NonShelterAnimal = 1 AND Neutered = 1) AS CatsNeuteredOutreach,

(SELECT COUNT(*) FROM animal 
 WHERE NeuteredDate >= '$@year$-01-01' AND NeuteredDate <= '$@year$-12-31'
 AND SpeciesID = 2
 AND NonShelterAnimal = 0 AND Neutered = 1 AND NeuteredDate >= DateBroughtIn 
 AND entrytypeID = 4) AS CatsNeuteredTNR,

(SELECT COUNT(*) FROM animal 
 WHERE ActiveMovementDate >= '$@year$-01-01' AND ActiveMovementDate <= '$@year$-12-31'
 AND ActiveMovementType = 1 AND SpeciesID = 1
 AND NonShelterAnimal = 0) AS DogsAdopted,

(SELECT COUNT(*) FROM animal 
 WHERE ActiveMovementDate >= '$@year$-01-01' AND ActiveMovementDate <= '$@year$-12-31'
 AND ActiveMovementType = 5 AND SpeciesID = 1
 AND NonShelterAnimal = 0) AS DogsReunited,

(SELECT COUNT(*) FROM animal 
 WHERE ActiveMovementDate >= '$@year$-01-01' AND ActiveMovementDate <= '$@year$-12-31'
 AND ActiveMovementType = 1 AND SpeciesID = 2
 AND NonShelterAnimal = 0) AS CatsAdopted,

(SELECT COUNT(*) FROM animal 
 WHERE ActiveMovementDate >= '$@year$-01-01' AND ActiveMovementDate <= '$@year$-12-31'
 AND ActiveMovementType = 5 AND SpeciesID = 2
 AND NonShelterAnimal = 0) AS CatsReunited,


(SELECT COUNT(*) FROM animal 
 INNER JOIN deathreason ON animal.PTSReasonID = deathreason.ID 
 WHERE DeceasedDate >= '$@year$-01-01' AND DeceasedDate <= '$@year$-12-31' AND PutToSleep = 1
 AND SpeciesID = 1 AND ReasonName LIKE '%Breed%' 
 AND NonShelterAnimal = 0) AS DogsPTSBanned,

(SELECT COUNT(*) FROM animal 
 INNER JOIN deathreason ON animal.PTSReasonID = deathreason.ID 
 WHERE DeceasedDate >= '$@year$-01-01' AND DeceasedDate <= '$@year$-12-31' AND PutToSleep = 1
 AND SpeciesID = 1 AND (ReasonName LIKE '%Sick%' OR ReasonName LIKE '%Injured%' OR ReasonName LIKE '%Medical%')
 AND NonShelterAnimal = 0) AS DogsPTSMedical,

(SELECT COUNT(*) FROM animal 
 INNER JOIN deathreason ON animal.PTSReasonID = deathreason.ID 
 WHERE DeceasedDate >= '$@year$-01-01' AND DeceasedDate <= '$@year$-12-31' AND PutToSleep = 1
 AND SpeciesID = 1 AND (ReasonName LIKE '%Aggression%' OR ReasonName LIKE '%Bit%' OR ReasonName LIKE '%Behaviour%')
 AND NonShelterAnimal = 0) AS DogsPTSBehaviour,

(SELECT COUNT(*) FROM animal 
 INNER JOIN deathreason ON animal.PTSReasonID = deathreason.ID 
 WHERE DeceasedDate >= '$@year$-01-01' AND DeceasedDate <= '$@year$-12-31' AND PutToSleep = 1
 AND SpeciesID = 1 AND (ReasonName NOT LIKE '%Aggression%' AND ReasonName NOT LIKE '%Bit%' AND ReasonName NOT LIKE '%Behaviour%' 
                        AND ReasonName NOT LIKE '%Medical%' AND ReasonName NOT LIKE '%Injured%' AND ReasonName NOT LIKE '%Sick%' 
                        AND ReasonName NOT LIKE '%Breed%')
 AND NonShelterAnimal = 0) AS DogsPTSOther,

(SELECT COUNT(*) FROM animal 
 INNER JOIN deathreason ON animal.PTSReasonID = deathreason.ID 
 WHERE DeceasedDate >= '$@year$-01-01' AND DeceasedDate <= '$@year$-12-31' AND PutToSleep = 1
 AND SpeciesID = 1 
 AND NonShelterAnimal = 0) AS DogsPTSTotal,

(SELECT COUNT(*) FROM animal 
 INNER JOIN deathreason ON animal.PTSReasonID = deathreason.ID 
 WHERE DeceasedDate >= '$@year$-01-01' AND DeceasedDate <= '$@year$-12-31' AND PutToSleep = 1
 AND SpeciesID = 2 AND (ReasonName LIKE '%Sick%' OR ReasonName LIKE '%Injured%' OR ReasonName LIKE '%Medical%')
 AND NonShelterAnimal = 0) AS CatsPTSMedical,

(SELECT COUNT(*) FROM animal 
 INNER JOIN deathreason ON animal.PTSReasonID = deathreason.ID 
 WHERE DeceasedDate >= '$@year$-01-01' AND DeceasedDate <= '$@year$-12-31' AND PutToSleep = 1
 AND SpeciesID = 2 AND (ReasonName LIKE '%Aggression%' OR ReasonName LIKE '%Bit%' OR ReasonName LIKE '%Behaviour%')
 AND NonShelterAnimal = 0) AS CatsPTSBehaviour,

(SELECT COUNT(*) FROM animal 
 INNER JOIN deathreason ON animal.PTSReasonID = deathreason.ID 
 WHERE DeceasedDate >= '$@year$-01-01' AND DeceasedDate <= '$@year$-12-31' AND PutToSleep = 1
 AND SpeciesID = 2 AND (ReasonName NOT LIKE '%Aggression%' AND ReasonName NOT LIKE '%Bit%' AND ReasonName NOT LIKE '%Behaviour%' 
                        AND ReasonName NOT LIKE '%Medical%' AND ReasonName NOT LIKE '%Injured%' AND ReasonName NOT LIKE '%Sick%' 
                        AND ReasonName NOT LIKE '%Breed%')
 AND NonShelterAnimal = 0) AS CatsPTSOther,

(SELECT COUNT(*) FROM animal 
 INNER JOIN deathreason ON animal.PTSReasonID = deathreason.ID 
 WHERE DeceasedDate >= '$@year$-01-01' AND DeceasedDate <= '$@year$-12-31' AND PutToSleep = 1
 AND SpeciesID = 2
 AND NonShelterAnimal = 0) AS CatsPTSTotal
 
FROM lksmovementtype LIMIT 1
###
$$HEADER
HEADER$$

$$BODY

<table border="1">
<tr>
<th></th>
<th>Strays (signed over by LA's)</th>
<th>Strays (brought in by public)</th>
<th>Surrendered by owner</th>
<th>From other sources</th>
<th>Total</th>
</tr>
<tr>
<td>How many dogs came into our care in $YEAR ?</td>
<td>0 *</td>
<td>$DOGSINTAKESTRAYPUBLIC</td>
<td>$DOGSINTAKESURRENDER</td>
<td>$DOGSINTAKEOTHER</td>
<td>$DOGSINTAKETOTAL</td>
</tr>
<tr>
<td>How many cats came into our care in $YEAR ?</td>
<td>N/A</td>
<td>$CATSINTAKESTRAYPUBLIC</td>
<td>$CATSINTAKESURRENDER</td>
<td>$CATSINTAKEOTHER</td>
<td>$CATSINTAKETOTAL</td>
</tr>

<tr>
<th></th>
<th>Microchip and records upto date</th>
<th>Microchip but data not accurate</th>
<th>Microchip but not registered</th>
<th>Overseas microchip</th>
<th>Total</th>
</tr>
<tr>
<td>How many dogs entering our care in $YEAR were microchipped on arrival?</td>
<td>0 *</td>
<td>$DOGSALREADYCHIPPED</td>
<td>0 *</td>
<td>0 *</td>
<td>$DOGSALREADYCHIPPED</td>
</tr>
<tr>
<td>How many cats entering our care in $YEAR were microchipped on arrival?</td>
<td>0 *</td>
<td>$CATSALREADYCHIPPED</td>
<td>0 *</td>
<td>0 *</td>
<td>$CATSALREADYCHIPPED</td>
</tr>

<tr>
<th></th>
<th>In our care</th>
<th>As part of outreach programme</th>
<th>As part of TNR scheme</th>  
</tr>
<tr>
<td>How many dogs were neutered in $YEAR ?</td>
<td>$DOGSNEUTEREDINCARE</td>
<td>$DOGSNEUTEREDOUTREACH</td>
<td>N/A</td>
</tr>
<tr>
<td>How many cats were neutered in $YEAR ?</td>
<td>$CATSNEUTEREDINCARE</td>
<td>$CATSNEUTEREDOUTREACH</td>
<td>$CATSNEUTEREDTNR</td>  
</tr>

<tr>
<th></th>
<th>Rehomed</th>
<th>Reunited with Owner</th>
</tr>
<tr>
<td>How many dogs did you rehome and reunite in $YEAR ?</td>
<td>$DOGSADOPTED</td>
<td>$DOGSREUNITED</td>
</tr>
<tr>
<td>How many cats did you rehome and reunite in $YEAR ?</td>
<td>$CATSADOPTED</td>
<td>$CATSREUNITED</td>
</tr>

<tr>
<th></th>
<th>Banned Breed</th>
<th>Medical</th>
<th>Behaviour</th>
<th>Other</th>
<th>Total</th>
</tr>
<tr>
<td>How many dogs were PTS in $YEAR and what were the reasons?</td>
<td>$DOGSPTSBANNED</td>
<td>$DOGSPTSMEDICAL</td>
<td>$DOGSPTSBEHAVIOUR</td>
<td>$DOGSPTSOTHER</td>
<td>$DOGSPTSTOTAL</td>
</tr>
<tr>
<td>How many cats were PTS in $YEAR and what were the reasons?</td>
<td>N/A</td>
<td>$CATSPTSMEDICAL</td>
<td>$CATSPTSBEHAVIOUR</td>
<td>$CATSPTSOTHER</td>
<td>$CATSPTSTOTAL</td>
</tr>
  
</table>

<p><i>* ASM has no way of calculating this value</i></p>

BODY$$

$$FOOTER
FOOTER$$
