VIC Government Figures
###
Figures
###
33600/Any rev01
###
Victoria government figures report.
###
en
###
$VAR from DATE Enter from date$
$VAR to DATE Enter to date$

SELECT 

'$@from$' AS FromDate, '$@to$' AS ToDate, 

(SELECT COUNT(*) FROM animal WHERE 
NOT EXISTS (SELECT MovementDate FROM adoption WHERE MovementDate < '$@from$' AND (ReturnDate Is Null OR ReturnDate >= '$@from$') AND MovementType NOT IN (2,8) AND AnimalID = animal.ID)
AND DateBroughtIn < '$@from$'
AND NonShelterAnimal = 0
AND SpeciesID = 1
AND (DeceasedDate Is Null OR DeceasedDate >= '$@from$')) AS DogBeginning,

(SELECT COUNT(*) FROM animal WHERE 
NOT EXISTS (SELECT MovementDate FROM adoption WHERE MovementDate < '$@from$' AND (ReturnDate Is Null OR ReturnDate >= '$@from$') AND MovementType NOT IN (2,8) AND AnimalID = animal.ID)
AND DateBroughtIn < '$@from$'
AND NonShelterAnimal = 0
AND SpeciesID = 2
AND (DeceasedDate Is Null OR DeceasedDate >= '$@from$')) AS CatBeginning,

(SELECT COUNT(*) FROM animal
WHERE MostRecentEntryDate >= '$@from$' AND MostRecentEntryDate <= '$@to$' 
AND SpeciesID = 1
AND NonShelterAnimal = 0) AS DogAdmissions,

(SELECT COUNT(*) FROM animal
WHERE MostRecentEntryDate >= '$@from$' AND MostRecentEntryDate <= '$@to$' 
AND SpeciesID = 2
AND NonShelterAnimal = 0) AS CatAdmissions,

(SELECT COUNT(*) FROM animal
INNER JOIN adoption ON animal.ID = adoption.AnimalID 
WHERE MovementType = 1
AND MovementDate >= '$@from$' AND MovementDate <= '$@to$' 
AND SpeciesID = 1
AND NonShelterAnimal = 0) AS DogRehomed,

(SELECT COUNT(*) FROM animal
INNER JOIN adoption ON animal.ID = adoption.AnimalID 
WHERE MovementType = 1
AND MovementDate >= '$@from$' AND MovementDate <= '$@to$' 
AND SpeciesID = 2
AND NonShelterAnimal = 0) AS CatRehomed,

(SELECT COUNT(*) FROM animal
INNER JOIN adoption ON animal.ID = adoption.AnimalID 
WHERE MovementType = 3
AND MovementDate >= '$@from$' AND MovementDate <= '$@to$' 
AND SpeciesID = 1
AND NonShelterAnimal = 0) AS DogTransferred,

(SELECT COUNT(*) FROM animal
INNER JOIN adoption ON animal.ID = adoption.AnimalID 
WHERE MovementType = 3
AND MovementDate >= '$@from$' AND MovementDate <= '$@to$' 
AND SpeciesID = 2
AND NonShelterAnimal = 0) AS CatTransferred,

(SELECT COUNT(*) FROM animal
INNER JOIN adoption ON animal.ID = adoption.AnimalID 
WHERE MovementType = 5
AND MovementDate >= '$@from$' AND MovementDate <= '$@to$' 
AND SpeciesID = 1
AND NonShelterAnimal = 0) AS DogReclaimed,

(SELECT COUNT(*) FROM animal
INNER JOIN adoption ON animal.ID = adoption.AnimalID 
WHERE MovementType = 5
AND MovementDate >= '$@from$' AND MovementDate <= '$@to$' 
AND SpeciesID = 2
AND NonShelterAnimal = 0) AS CatReclaimed

###
$$HEADER
<table border="1">
<thead>
<tr>
<th></th>
<th>Dog</th>
<th>Cat</th>
</tr>
</thead>
<tbody>
HEADER$$

$$BODY
<tr>
<td>in care at the establishment, or in foster care, on $FROMDATE</td>
<td>$DogBeginning</td>
<td>$CatBeginning</td>
</tr>
<tr>
<td>accepted for admission</td>
<td>$DogAdmissions</td>
<td>$CatAdmissions</td>
</tr>
<tr>
<td>permanently transferred to other facilities or groups for rehoming</td>
<td>$DogTransferred</td>
<td>$CatTransferred</td>
</tr>
<tr>
<td>reclaimed by their owner</td>
<td>$DogReclaimed</td>
<td>$CatReclaimed</td>
</tr>
<tr>
<td>rehomed</td>
<td>$DogRehomed</td>
<td>$CatRehomed</td>
</tr>
BODY$$

$$FOOTER
</tbody>
</table>
FOOTER$$

