> how would I use HAVING to select out where the difference between
> reportsInDatabase and DistinctTimes is above 15%
> select r.IpAddressNumber, v.VehicleIp,
> Count(r.ReportId) as ReportsInDatabase,
> Count(DISTINCT Time) as DistinctTimes, v.VehicleName, c.companyName
> from reportsView r
> INNER JOIN
> vehicletable v
> on v.IpAddressNumber = r.IpAddressNum
> INNER JOIN
> companyTable c
> on v.companyIdNumber = c.companyIdNumber
> where r.currentDate between '09-29-2003 00:00:00.000' and '09-29-2003
> 23:59:59.999'
> Group By v.IpAddressNumber, r.IpAddressNum, v.VehicleName,
> v.VehicleIp, c.companyName
> Order by ReportsInDatabase DESC
SELECT
r.IpAddressNumber,
v.VehicleIp,
Count(r.ReportId) as ReportsInDatabase,
Count(DISTINCT Time) as DistinctTimes,
v.VehicleName,
c.companyName
FROM
reportsView r INNER JOIN vehicletable v
ON v.IpAddressNumber = r.IpAddressNum
INNER JOIN companyTable c
ON v.companyIdNumber = c.companyIdNumber
WHERE
r.currentDate BETWEEN '09-29-2003 00:00:00.000'
AND '09-29-2003 23:59:59.999'
GROUP BY
v.IpAddressNumber,
r.IpAddressNum,
v.VehicleName,
v.VehicleIp,
c.companyName
HAVING
Abs((ReportsInDatabase-DistinctTimes)/ReportsInDatabase)
> 0.15
ORDER BY
ReportsInDatabase DESC
Jochem
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
