ADCH Annual Survey (2024)
###
Figures
###
34801/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 EntryTypeID = 2) AS DogsIntakeStrayPublic,

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

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

(SELECT COUNT(*) FROM animal
 WHERE DateBroughtIn >= '$@year$-01-01' AND DateBroughtIn <= '$@year$-12-31'
 AND SpeciesID = 1 
 AND NonShelterAnimal = 0 AND Archived = 0) AS DogsIntakeTotal,
 
(SELECT COUNT(*) FROM animal WHERE
NOT EXISTS (SELECT MovementDate FROM adoption WHERE MovementDate <= '$@year$-12-31' 
AND (ReturnDate Is Null OR ReturnDate > '$@year$-12-31') 
AND MovementType NOT IN (2,8) AND AnimalID = animal.ID)
AND DateBroughtIn <= '$@year$-12-31 23:59:59'
AND NonShelterAnimal = 0
AND SpeciesID = 1
AND (DeceasedDate Is Null OR DeceasedDate > '$@year$-12-31')) AS DogsAtDate,

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

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

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

(SELECT COUNT(*) FROM animal
 WHERE DateBroughtIn >= '$@year$-01-01' AND DateBroughtIn <= '$@year$-12-31'
 AND SpeciesID = 2 
 AND NonShelterAnimal = 0 AND Archived = 0) AS CatsIntakeTotal,
 
(SELECT COUNT(*) FROM animal WHERE
NOT EXISTS (SELECT MovementDate FROM adoption WHERE MovementDate <= '$@year$-12-31' 
AND (ReturnDate Is Null OR ReturnDate > '$@year$-12-31') 
AND MovementType NOT IN (2,8) AND AnimalID = animal.ID)
AND DateBroughtIn <= '$@year$-12-31 23:59:59'
AND NonShelterAnimal = 0
AND SpeciesID = 2
AND (DeceasedDate Is Null OR DeceasedDate > '$@year$-12-31')) AS CatsAtDate, 

(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,
  
(SELECT COUNT(*) FROM animalwaitinglist 
 WHERE DatePutOnList >= '$@year$-01-01' AND (DateRemovedFromList > '$@year$-12-31' OR DateRemovedFromList IS NULL)
 AND SpeciesID = 2) AS WaitingListCats,
 
 (SELECT COUNT(*) FROM animalwaitinglist 
 WHERE DatePutOnList >= '$@year$-01-01' AND (DateRemovedFromList > '$@year$-12-31' OR DateRemovedFromList IS NULL)
 AND SpeciesID = 1) AS WaitingListDogs
 
FROM lksmovementtype LIMIT 1
###
$$HEADER
HEADER$$

$$BODY
<h2>Dogs</h2>
<p></p>
<p>10a. On 31 December $YEAR how many dogs did you have in your care?: <b>$DOGSATDATE</b></p>
<p></p>
<p>10c. On 31 December $YEAR what was your waiting list for dog intake?: <b>$WAITINGLISTDOGS</b></p>
<p></p> 
<p>11b. How many dogs came into your care in $YEAR as strays brought in by the public?: <b>$DOGSINTAKESTRAYPUBLIC</b></p>
<p></p>
<p>11c. How many dogs came into your care in $YEAR relinquished by owners?: <b>$DOGSINTAKESURRENDER</b></p>
<p></p>
<p>11d. How many dogs came into your care in $YEAR from other sources?: <b>$DOGSINTAKEOTHER</b></p>
<p></p>
<p>12a. How many dogs in your care have you neutered in $YEAR: <b>$DOGSNEUTEREDINCARE</b></p>
<p></p>
<p>12b. How many dogs have you neutered in $YEAR as part of a community outreach programme?: <b>$DOGSNEUTEREDOUTREACH</b><p/>
<p></p>
<p>13. How many dogs did you reunite in $YEAR ?: <b>$DOGSREUNITED</b></p>
<p></p>
<p>14. How many dogs did you rehome in $YEAR ?: <b>$DOGSADOPTED</b></p>
<p></p>
<p>15a. How many banned breeds were put to sleep in $YEAR ?: <b>$DOGSPTSBANNED</b></p>
<p></p>
<p>15b. How many dogs were put to sleep for medical reasons in $YEAR ?: <b>$DOGSPTSMEDICAL</b></p>
<p></p>
<p>15c. How many dogs were put to sleep for behavioural reasons in $YEAR ?: <b>$DOGSPTSBEHAVIOUR</b></p>
<p></p>
<p>15d. How many dogs were put to sleep for other reasons in $YEAR ?: <b>$DOGSPTSOTHER</b></p>
<p></p>
<h2>Cats</h2>
<p></p>
<p>16a. On 31 December $YEAR how many cats did you have in your care?: <b>$CATSATDATE</b></p>
<p></p>
<p>16c. On 31 December $YEAR what was your waiting list for cat intake?: <b>$WAITINGLISTCATS</b></p>
<p></p>
<p>17a. How many cats came into your care in $YEAR as strays?: <b>$CATSINTAKESTRAYPUBLIC</b></p>
<p></p>
<p>17b. How many cats came into your care in $YEAR relinquished by owners?: <b>$CATSINTAKESURRENDER</b></p>
<p></p>
<p>17c. How many cats came into your care in $YEAR from other sources?: <b>$CATSINTAKEOTHER</b></p>
<p></p>
<p>18a. How many cats in your care have you neutered in $YEAR ?: <b>$CATSNEUTEREDINCARE</b></p>
<p></p>
<p>18b. How many cats have you neutered in $YEAR as part of a community outreach programme?: <b>$CATSNEUTEREDOUTREACH</b><p/> 
<p></p>
<p>18c. How many cats have you neutered as part of a TNR scheme in $YEAR ?: <b>$CATSNEUTEREDTNR</b></p>
<p></p>
<p>19. How many cats did you reunite in $YEAR ?: <b>$CATSREUNITED</b></p>
<p></p>
<p>20. How many cats did you rehome in $YEAR ?: <b>$CATSADOPTED</b></p>
<p></p>
<p>21a. How many cats were put to sleep for medical reasons in $YEAR ?: <b>$CATSPTSMEDICAL</b></p>
<p></p>
<p>21b. How many cats were put to sleep for behavioural reasons in $YEAR ?: <b>$CATSPTSBEHAVIOUR</b></p>
<p></p>
<p>21c. How many cats were put to sleep for other reasons in $YEAR ?: <b>$CATSPTSOTHER</b></p>
BODY$$

$$FOOTER
FOOTER$$