Adopted animals with a vacc, test or medical due between two dates
###
Medical
###
34701/Any rev02
###
Mailmerge source of owner and animal information for all adoptions where the animal has a vaccination, test or medical item due between two dates
###
en
###
$VAR from DATE Medical items due between$
$VAR to DATE and$

SELECT owner.ID, owner.OwnerName, owner.OwnerAddress, owner.OwnerPostcode,
animal.AnimalName, 
owner.EmailAddress, owner.EmailAddress2, owner.OwnerTown, owner.OwnerCounty, 
owner.OwnerTitle, owner.OwnerInitials, owner.OwnerForenames, owner.OwnerSurname,
animalvaccination.DateRequired, vaccinationtype.VaccinationType AS MedicalType
FROM animal
INNER JOIN adoption ON adoption.ID = animal.ActiveMovementID  
INNER JOIN owner ON adoption.OwnerID = owner.ID
INNER JOIN animalvaccination ON animalvaccination.AnimalID = animal.ID 
INNER JOIN vaccinationtype ON vaccinationtype.ID = animalvaccination.VaccinationID 
WHERE animal.ActiveMovementType = 1 AND animal.Archived = 1 
AND animal.DeceasedDate Is Null
AND animalvaccination.DateOfVaccination Is Null 
AND animalvaccination.DateRequired >= '$@from$'
AND animalvaccination.DateRequired <= '$@to$'
AND owner.ExcludeFromBulkEmail = 0

UNION SELECT owner.ID, owner.OwnerName, owner.OwnerAddress, owner.OwnerPostcode,
animal.AnimalName, 
owner.EmailAddress, owner.EmailAddress2, owner.OwnerTown, owner.OwnerCounty, 
owner.OwnerTitle, owner.OwnerInitials, owner.OwnerForenames, owner.OwnerSurname,
animaltest.DateRequired, testtype.TestName AS MedicalType
FROM animal
INNER JOIN adoption ON adoption.ID = animal.ActiveMovementID  
INNER JOIN owner ON adoption.OwnerID = owner.ID
INNER JOIN animaltest ON animaltest.AnimalID = animal.ID 
INNER JOIN testtype ON testtype.ID = animaltest.TestTypeID 
WHERE animal.ActiveMovementType = 1 AND animal.Archived = 1 
AND animal.DeceasedDate Is Null
AND animaltest.DateOfTest Is Null 
AND animaltest.DateRequired >= '$@from$'
AND animaltest.DateRequired <= '$@to$'
AND owner.ExcludeFromBulkEmail = 0

UNION SELECT owner.ID, owner.OwnerName, owner.OwnerAddress, owner.OwnerPostcode,
animal.AnimalName, 
owner.EmailAddress, owner.EmailAddress2, owner.OwnerTown, owner.OwnerCounty, 
owner.OwnerTitle, owner.OwnerInitials, owner.OwnerForenames, owner.OwnerSurname,
animalmedicaltreatment.DateRequired, animalmedical.TreatmentName AS MedicalType
FROM animal
INNER JOIN adoption ON adoption.ID = animal.ActiveMovementID  
INNER JOIN owner ON adoption.OwnerID = owner.ID
INNER JOIN animalmedical ON animalmedical.AnimalID = animal.ID 
INNER JOIN animalmedicaltreatment ON animalmedicaltreatment.AnimalMedicalID = animalmedical.ID 
WHERE animal.ActiveMovementType = 1 AND animal.Archived = 1 
AND animal.DeceasedDate Is Null
AND animalmedicaltreatment.DateGiven Is Null 
AND animalmedicaltreatment.DateRequired >= '$@from$'
AND animalmedicaltreatment.DateRequired <= '$@to$'
AND owner.ExcludeFromBulkEmail = 0

ORDER BY OwnerSurname
###
MAIL
