Expired licenses in the last year
###
Animal Control
###
34800/Any rev05
###
People on file with expired licenses in the last year and no renewal, where the animal is still alive
###
en
###
SELECT
ol.LicenceNumber,
lt.LicenceTypeName,
o.OwnerName, o.ID AS OID, a.AnimalName, a.ID AS AID,
ol.IssueDate, ol.ExpiryDate, ol.Comments
FROM ownerlicence ol
INNER JOIN licencetype lt ON lt.ID = ol.LicenceTypeID
INNER JOIN owner o ON o.ID = ol.OwnerID
LEFT OUTER JOIN animal a ON a.ID = ol.AnimalID 
WHERE ol.ExpiryDate <= CURRENT_DATE AND ol.ExpiryDate > '$CURRENT_DATE-365$'
AND ol.Renewed = 0
AND (ol.AnimalID = 0 OR a.DeceasedDate Is Null) 
ORDER BY ol.ExpiryDate
###
$$HEADER
<table border="1">
<thead>
<tr>
<th>Number</th>
<th>Type</th>
<th>Person</th>
<th>Animal</th>
<th>Issued</th>
<th>Expires</th>
<th>Comments</th>
</tr>
</thead>
<tbody>
HEADER$$

$$BODY
<tr>
<td>$LICENCENUMBER</td>
<td>$LICENCETYPENAME</td>
<td><a target="_blank" href="person?id=$OID">$OWNERNAME</a></td>
<td><a target="_blank" href="animal?id=$AID">$ANIMALNAME</a></td>
<td>$ISSUEDATE</td>
<td>$EXPIRYDATE</td>
<td>$COMMENTS</td>
</tr>
BODY$$

$$FOOTER
</tbody>
</table>
FOOTER$$
&&&
Expired licenses in the last year by animal
###
Animal Control
###
33307/Any rev04
###
Animals on file where their latest license has expired in the last year
###
en
###
SELECT
ol.LicenceNumber,
lt.LicenceTypeName,
o.OwnerName, o.ID AS OID, a.AnimalName, a.ID AS AID,
ol.IssueDate, ol.ExpiryDate, ol.Comments
FROM animal a
INNER JOIN ownerlicence ol ON ol.AnimalID = a.ID AND ol.ID = (SELECT ID FROM ownerlicence WHERE AnimalID = a.ID ORDER BY ExpiryDate DESC LIMIT 1)
INNER JOIN licencetype lt ON lt.ID = ol.LicenceTypeID
INNER JOIN owner o ON o.ID = ol.OwnerID
WHERE ol.ExpiryDate <= '$CURRENT_DATE$' AND ol.ExpiryDate > '$CURRENT_DATE-365$'
AND a.DeceasedDate Is Null 
ORDER BY a.AnimalName, ol.ExpiryDate
###
$$HEADER
<table border="1">
<thead>
<tr>
<th>Number</th>
<th>Type</th>
<th>Person</th>
<th>Animal</th>
<th>Issued</th>
<th>Expires</th>
<th>Comments</th>
</tr>
</thead>
<tbody>
HEADER$$

$$BODY
<tr>
<td>$LICENCENUMBER</td>
<td>$LICENCETYPENAME</td>
<td><a target="_blank" href="person_licence?id=$OID">$OWNERNAME</a></td>
<td><a target="_blank" href="animal_licence?id=$AID">$ANIMALNAME</a></td>
<td>$ISSUEDATE</td>
<td>$EXPIRYDATE</td>
<td>$COMMENTS</td>
</tr>
BODY$$

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