Just for a simple list of users and a count of unique CallingStationID's in
the table, you can use:
SELECT UserName, COUNT(DISTINCT CallingStationID) AS Num
FROM UsageData_OnlineHours
GROUP BY Username
HAVING COUNT(DISTINCT CallingStationID) > 1
If you want a list of the phone numbers, you can join the above query back
to a second one that displays the numbers:
SELECT *
FROM (
SELECT UserName, CallingStationID
FROM UsageData_OnlineHours
GROUP BY Username, CallingStationID
) AS PhoneNumbers
INNER JOIN (
SELECT UserName, COUNT(DISTINCT CallingStationID) AS Num
FROM UsageData_OnlineHours
GROUP BY Username
HAVING COUNT(DISTINCT CallingStationID) > 1
) AS NumCount
ON PhoneNumbers.UserName = NumCount.UserName
ORDER BY PhoneNumbers.UserName
This works for Rodopi 5.2 sp3. I can't imagine they changed anything from
5.1 that would break this query.
Scott Collins
ISDN-Net
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Behalf Of Fred
Sent: Wednesday, May 04, 2005 4:41 PM
To: [email protected]
Subject: [Rodopi] another sql script
How about an SQL script to check the online usage table for accounts which
show connections from 2 or more different telephone numbers? We are using
Vircom Radius and Rodopi 5.1. I can tweak the script to make it work, just
don't know an easy way to accomplish this.
Thanks,
Frederic Tarasevicius
Internet Information Services, Inc.
http://www.i-is.com/
810-794-4400
---------------------
To Leave the Rodopi mail list send a message to [EMAIL PROTECTED]
with the word LEAVE as the message body.
Please also visit the Rodopi FAQ at http://www.rodopi-faq.com
---------------------
---------------------
To Leave the Rodopi mail list send a message to [EMAIL PROTECTED]
with the word LEAVE as the message body.
Please also visit the Rodopi FAQ at http://www.rodopi-faq.com
---------------------
