Home› End User
ReportBuilder 22.04 now available including Delphi 12.1 support!
New Blog Post: Helper Routines

Group search for min/max date field

rbuserrbuser
edited June 2013 in End User
Hi,

I'm trying to generate the equivalent of following query using the RB query
designer:

select custnum, max(transdate) from cust
join invoice on cust.custnum = invoice.custnum
group by custnum
having max(transdate) <= DATE '2013-01-01'


After building the query in the RB query designer, it generates the
following SQL:


SELECT cust.CustNum,
MAX(invoice.TransDate) MAX_invoice_TransDate
FROM cust cust INNER JOIN invoice invoice ON
(invoice.CustNum = cust.CustNum)
GROUP BY cust.CustNum
HAVING ( MAX(invoice.TransDate) <= )

In short, it's missing the comparison value and closing parenthesis if the
group search criteria value is entered directly in the value column of the
query designer. But it does generate the proper query when using a
parameter. It also works properly for VarChar or Numerical fields, just
not date fields.

Any chance we can get a patch for this so that manually entered values will
work with min/max values on a date field?

Details:
Report Builder Version: 14.08
Delphi Version: XE
Database: ElevateDB


---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
http://www.atrex.com
---------------------------------------

Comments

  • nicociziknicocizik
    edited June 2013
    Hi Terry,

    What format are you using to enter the date? Dade expects the standard
    machine locale-defined date format when entering dates into a query.

    For instance, if your machine is set up display dates as mm/dd/yyyy, you
    would need to enter the date as 01/01/2013. This would then get
    converted to a TDateTime type and then converted again to the yyyy-mm-dd
    format that ElevateDB expects according to the specifications in the
    daElevateDB.pas file.

    http://www.digital-metaphors.com/rbWiki/DADE/Fundamentals/Date_Formatting

    I created a quick test on my machine with Delphi XE and the latest
    version of ElevateDB and this functioned correctly.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • rbuserrbuser
    edited June 2013
    Hi Nico,


    01/05/2013 for Jan 1st 2013, which is what the system was set to for the
    date format (MM/dd/yyyy).



    Looks like it was a glitch in my Windows settings. I've seen some issues
    with Windows 7 and Delphi apps not using the selected date format, and it
    appears that I ran into it as well. I had to change and apply the windows
    language and region to something other than what it was set to, change them
    back and apply the settings. Once I did that, the group search started
    working as expected.

    Thank you for taking the time to verify this and I'm sorry to have wasted
    your time.

    ---------------------------------------
    Terry Swiers
    Millennium Software, Inc.
    http://www.1000years.com
    http://www.atrex.com
    ---------------------------------------
This discussion has been closed.