Transports completed between two dates by transport type
###
Transport
###
34004/Any rev03
###
Transports completed between two dates grouped by transport type
###
en
###
SELECT t.*, 
d.OwnerName AS DriverName, d.ID AS DID, d.OwnerAddress AS DriverAddress, d.OwnerTown AS DriverTown, d.OwnerCounty AS DriverCounty, d.OwnerPostcode AS DriverPostcode, d.HomeTelephone AS DriverTelephone, 
p.OwnerName AS PickupName, p.ID AS PID, t.PickupAddress, t.PickupTown, t.PickupCounty, t.PickupPostcode,
dr.OwnerName AS DropoffName, dr.ID AS DRID, t.DropoffAddress, t.DropoffTown, t.DropoffCounty, t.DropoffPostcode,
a.AnimalName, a.ID AS AID, a.ShelterCode, s.SpeciesName, a.BreedName, tt.transporttypename,
CASE WHEN t.Status = 1 THEN 'New' 
WHEN t.Status = 2 THEN 'Confirmed'
WHEN t.Status = 3 THEN 'Hold'
WHEN t.Status = 4 THEN 'Scheduled'
WHEN t.Status = 10 THEN 'Cancelled'
WHEN t.Status = 11 THEN 'Completed' END AS StatusName
FROM animaltransport t
LEFT OUTER JOIN animal a ON a.ID = t.AnimalID
LEFT OUTER JOIN species s ON a.SpeciesID = s.ID
LEFT OUTER JOIN owner d ON d.ID = t.DriverOwnerID
LEFT OUTER JOIN owner p ON p.ID = t.PickupOwnerID
LEFT OUTER JOIN owner dr ON dr.ID = t.DropoffOwnerID
INNER JOIN transporttype tt ON tt.ID = t.TransportTypeID
WHERE t.Status = 11 AND t.DropoffDateTime >= '$ASK DATE Enter date from$' AND t.DropoffDateTime <= '$ASK DATE Enter date to$' 
ORDER BY t.DropoffDateTime
###
$$HEADER
HEADER$$

$$GROUP_TransportTypeName
$$HEAD
<h2>$TransportTypeName</h2>
<table border="1">
<thead>
<tr>
<th>Animal</th>
<th>Driver</th>
<th>Pickup</th>
<th>from</th>
<th>Dropoff</th>
<th>at</th>
<th>Status</th>
<th>Miles</th>
<th>Cost</th>
<th>Comments</th>
</tr>
</thead>
<tbody>
$$FOOT
</tbody>
</table>
GROUP$$
$$BODY
<tr>
<td><a target="_blank" href="animal?id=$AID">$ANIMALNAME - $SHELTERCODE</a> ($BREEDNAME $SPECIESNAME)</td>
<td><a target="_blank" href="person?id=$DID">$DRIVERNAME</a><br/>$DRIVERADDRESS<br/>$DRIVERTOWN $DRIVERCOUNTY $DRIVERPOSTCODE</td>
<td><a target="_blank" href="person?id=$PID">$PICKUPNAME</a><br/>$PICKUPADDRESS<br/>$PICKUPTOWN $PICKUPCOUNTY $PICKUPPOSTCODE</td>
<td>$PICKUPDATETIME</td>
<td><a target="_blank" href="person?id=$DRID">$DROPOFFNAME</a><br/>$DROPOFFADDRESS<br/>$DROPOFFTOWN $DROPOFFCOUNTY $DROPOFFPOSTCODE</td>
<td>$DROPOFFDATETIME</td>
<td>$STATUSNAME</td>
<td>$MILES</td>
<td>$COST</td>
<td>$COMMENTS</td>
</tr>
BODY$$

$$FOOTER
FOOTER$$

