On 3/26/2016 4:36 PM, shawn l.green wrote:
On 3/25/2016 6:39 AM, JAHANZAIB SYED wrote:
I have Freeradius 2.x with MySQL 5.5 in Ubuntu.
I want to query user quota for current date. I am using following code
SELECT (SUM(acctinputoctets)+SUM(acctoutputoctets)) AS Total FROM
radacct where (acctstarttime between DATE_FORMAT(NOW(),'%Y-%m-%d')
AND NOW() AND acctstoptime between DATE_FORMAT(NOW() ,'%Y-%m-%d')
AND NOW()) AND radacct.username='%{User-Name}'
It works fine if there is acctstoptime value in table. but if user
have not disconnected yet (and have no previous session for today) it
returns NULL.
So how can i can get the value even if user acttstoptime is null?
Try this...(using an earlier suggestion to the thread)
SELECT
SUM(acctinputoctets + acctoutputoctets) AS Total
FROM radacct
WHERE
radacct.username='%{User-Name}'
AND acctstarttime BETWEEN CURDATE() AND NOW()
AND (
acctstoptime <= NOW()
OR acctstoptime IS NULL
)
But in reality, can you have an acctstarttime that is >= NOW()? If not,
then you can also simplify that term to just
oops! one too many AND's
AND AND acctstarttime >= CURDATE()
I meant to write
AND acctstarttime >= CURDATE()
and lose the BETWEEN comparison.
--
Shawn
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql