Licenses expiring between two dates
###
Animal Control
###
34800/Any rev05
###
Licenses that expire between the two dates given and have not been renewed and the animal is still alive
###
en
###
SELECT
ol.LicenceNumber,
lt.LicenceTypeName,
ol.OwnerID, o.OwnerName, o.ID AS OID, o.OwnerAddress, o.OwnerTown, o.OwnerCounty, o.OwnerPostcode,
o.HomeTelephone, o.WorkTelephone, o.MobileTelephone, o.EmailAddress,
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 >= '$ASK DATE Enter the from date$'
AND ol.ExpiryDate <= '$ASK DATE Enter the to date$' AND a.DeceasedDate Is Null
AND (ol.AnimalID = 0 OR a.DeceasedDate Is Null)
AND ol.Renewed = 0
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_licence?id=$OID">$OWNERNAME</a><br/>
$OwnerAddress<br/>
$OwnerTown<br/>
$OwnerCounty<br/>
$OwnerPostcode<br/>
$EmailAddress<br/>
H: $HOMETELEPHONE, W: $WORKTELEPHONE, C: $MOBILETELEPHONE</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$$

