Re: [libreoffice-users] Re: Base/SQL help

On Tue, Jan 3, 2012 at 9:37 AM, Andreas Säger <villeroy@t-online.de> wrote:
> 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
> ...
>
> --
Thank you for mentioning datediff
"SELECT * FROM `modifiedprintusage` where
DATEDIFF(CURRENT_DATE,`date`)<3 ORDER BY `ip` DESC;"
works great from the commandline mysql but libreoffice is rejecting it.


> 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

-- 
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