Licenses expiring between two dates by jurisdiction
###
Animal Control
###
34800/Any rev05
###
Licenses that expire between the two dates given by jurisdiction, excludes people with renewed licenses and deceased animals
###
en
###
SELECT
ol.LicenceNumber,
lt.LicenceTypeName,
j.JurisdictionName, 
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 
LEFT OUTER JOIN jurisdiction j ON j.ID = o.JurisdictionID
WHERE ol.ExpiryDate >= '$ASK DATE Expiring between $'
AND ol.ExpiryDate <= '$ASK DATE and $' AND a.DeceasedDate Is Null 
AND ol.Renewed = 0
AND (ol.AnimalID = 0 OR a.DeceasedDate Is Null)
ORDER BY j.JurisdictionName, ol.ExpiryDate
###
$$HEADER
HEADER$$

$$GROUP_JurisdictionName
$$HEAD
<h2>$JurisdictionName</h2>
<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>
$$FOOT
</tbody>
</table>
<p>Total $JurisdictionName: {COUNT.JurisdictionName}</p>
GROUP$$

$$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
FOOTER$$
