function, which I presume is for absolute number
anyway, now I get this....
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ReportsInDatabase'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'DistinctTimes'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'ReportsInDatabase'.
...tony
tony weeg
senior web applications architect
navtrak, inc.
www.navtrak.net
[EMAIL PROTECTED]
410.548.2337
-----Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 11:10 AM
To: CF-Talk
Subject: Re: ot: sql question
Tony Weeg wrote:
> 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]
