[libreoffice-users] Re: Base/SQL help

All the wizards and graphical tools are more or less broken. What the query
designer can do properly can be done by SQL learners after the very first
lessons had been learned. Anything slightly advanced has to be done in plain
SQL.

MySQL has a datediff function:
http://www.w3schools.com/sql/func_datediff_mysql.asp

In Base syntax (parsed mode, should also work in graphical view):
SELECT "date" AS "Date","ip"||' - '||"serial" AS "Printer", SUM("counter")AS
"Sum"
FROM "modifiedprintusage"
WHERE DATEDIFF('dd', CURRENT_DATE, "date")<3
GROUP BY "date","ip","serial"

In MySQL syntax (direct SQL mode)
In Base syntax:
SELECT `date` AS `Date`,`ip`||' - '||`serial` AS `Printer`, SUM(`counter`)AS
`Sum`
FROM `modifiedprintusage`
WHERE DATEDIFF( CURDATE(), `date`)<3
GROUP BY `date`,`ip`,`serial`

It is supposed to return this 3-column table:
Date  |  Printer  |  Sum
2012-01-02 | 192.168.023 - Epson Blurb  |  232
2012-01-02 | 192.168.024 - HP Spitfire  |  1023
...

--
View this message in context: 
http://nabble.documentfoundation.org/Base-SQL-help-tp3627698p3629585.html
Sent from the Users mailing list archive at Nabble.com.

-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted