I'm trying to clean up a query which gathers its data based on random sampling times and number of times in the sample period.
The purpose is to track vehicles in our building visitor parking stalls in an effort to identify units using the spots as extra spots to their assigned (downtown, very old building, parking is an absolute premium so parking abuse is very common). The problem is that the number of spaces are not always occupied (so I cannot use that in the query calculation), and the times I sample are random - some days I'll only sample twice, once in the morning, once in the evening, other days I'll sample several times over every couple of hours.
The structure is like so (removed the irrelevant):
Code:
CREATE TABLE `visitorparking` (
  `vehicleId` binary(16) NOT NULL,
  `unitNumber` int(10) unsigned NOT NULL DEFAULT '0',
  `onDate` datetime NOT NULL,
  PRIMARY KEY (`vehicleId`,`unitNumber`,`onDate`),
  CONSTRAINT `fk_visitorparking_vehicleid` FOREIGN KEY (`vehicleId`) REFERENCES
`vehicle` (`vehicleId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `vehicle` (
  `vehicleId` binary(16) NOT NULL,
  `licensePlateNumber` varchar(20) NOT NULL,
  PRIMARY KEY (`vehicleId`),
  UNIQUE KEY `licensePlateNumber_uk` (`licensePlateNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER VIEW `view_sampledates` AS select count(distinct cast(`visitorparking`.`onDate` as time)) AS `sampleCount`,cast(`visitorparking`.`onDate` as date) AS `onDate` from `visitorparking` group by cast(`visitorparking`.`onDate` as date)
And the queries are (one for unit percentage, and one for vehicle percentage):
Code:
-- Parked Percent: By Unit
SELECT IF(unitNumber = 0, 'NO PERMIT', unitNumber) AS unitNumber, CONCAT(ROUND(100 * COUNT(*) /
(
	SELECT SUM(sampleCount)
	FROM view_sampledates
	WHERE onDate BETWEEN '2014-02-02' AND '2014-02-09'
), 2), '%') AS parkedPercent
FROM visitorparking
WHERE onDate BETWEEN '2014-02-02' AND '2014-02-09'
GROUP BY unitNumber
ORDER BY COUNT(*) DESC;

-- Parked Percent: By License Plate Number
SELECT v.licensePlateNumber, CONCAT(ROUND(100 * COUNT(*) / 
(
	SELECT SUM(sampleCount)
	FROM view_sampledates
	WHERE onDate BETWEEN '2014-02-02' AND '2014-02-09'
), 2), '%') AS parkedPercent
FROM visitorparking vp
INNER JOIN vehicle v ON v.vehicleId = vp.vehicleId
WHERE onDate BETWEEN '2014-02-02' AND '2014-02-09'
GROUP BY v.licensePlateNumber
ORDER BY COUNT(*) DESC;
Both of them seem to do the job I want by providing me a percentage for either by unit or vehicle, but are both very ugly - specifically the entry for the where date range in more than one location.
Is it even possible to clean it up given the variable sampling I'm using?