Expiring licenses between two dates
###
Animal Control
###
34800/Any rev04
###
Licenses that expire between the two dates given with owner details, excludes people who have renewed and deceased animals
###
en
###
SELECT DISTINCT ol.*, o.OwnerName, o.OwnerForeNames, o.OwnerSurname, o.OwnerAddress, o.OwnerTown, o.OwnerCounty, o.OwnerPostcode, o.EmailAddress, o.EmailAddress2,
a.AnimalName, a.ShelterCode, a.AnimalAge, a.BreedName, sx.Sex, s.SpeciesName, 
rv.DateOfVaccination AS RabiesGiven, rv.DateExpires AS RabiesExpires, rvet.OwnerName AS RabiesVetName, rvet.OwnerTown AS RabiesVetCity
FROM ownerlicence ol
INNER JOIN owner o ON o.ID = ol.OwnerID
LEFT OUTER JOIN animal a ON a.ID = ol.AnimalID
LEFT OUTER JOIN animalvaccination rv ON rv.ID = (SELECT MAX(ID) FROM animalvaccination WHERE AnimalID = a.ID AND VaccinationID = 4 AND DateOfVaccination Is Not Null)
LEFT OUTER JOIN owner rvet ON rvet.ID = rv.AdministeringVetID 
LEFT OUTER JOIN species s ON s.ID = a.SpeciesID
LEFT OUTER JOIN lksex sx ON sx.ID = a.Sex
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.Renewed = 0
AND (ol.AnimalID = 0 OR a.DeceasedDate Is Null)
ORDER BY ol.ExpiryDate
###
MAIL
