Date parameter not inclusive

[MrEgg964]

Hello all,

I need your help as I am running out of ideas here.

In a query, I want to select all records WHERE Act.End <= :Mois -- where Act.End is a DATE/TIME field and :Mois is a user parameter (the user enters the last day of the month he wants to query).

The problem is that Act.End contains times. If :Mois = 31/07/2014, the query won't return any records for that date, because Act.End has values like 31/07/2014 09:30. In other words, the query acts as Act.End < :Mois rather than <= :Mois

Directly in MySQL, I could solve this issue with WHERE Act.End < @Mois + INTERVAL 1 DAY

How can I achieve the same thing directly in Base, provided that I need to keep this :Mois to be dynamically entered by the user?

EDIT:
I forgot to mention that I am using LO 4, connecting to a MySQL server.

Thank you

[Villeroy]

May be
WHERE Act.End <= CAST(CONCAT(:Mois , ' 23:59:59') AS DATETIME)
(did not try)

[MrEgg964]

Thank you for the suggestion, but unfortunately no, this is not working.

Is there no way to add 1 day to the parameter, just like + INTERVAL 1 DAY is doing?

[Villeroy]

Create a view. A view will be executed by your MySQL database.
Then create a parameter query from that view. Unfortunately, views return read-only record sets.

[MrEgg964]

Maybe I should have started by posting my SQL code for clarity. This is directly from MySQL, not from Base, and it is what I'm trying to achieve in Base.

Code: Select all

    SET @Mois:='2014/07/31';
    SELECT 
        Act.ClientID,
        Act.Start,
        Cat.Category,
        SUM(TIME_TO_SEC(TIMEDIFF(End, Start))) AS Duration,
        Act.End
    FROM
        x_pect.Category Cat,
        x_pect.Activity Act
    WHERE
        Cat.CategoryID = Act.CategoryID
		AND Act.End < @Mois + INTERVAL 1 DAY
    GROUP BY Act.ClientID , Cat.Category
    ORDER BY Act.ClientID ASC;

Ignore the fact that the parameter is already set, the whole point of my trying to use Base being to use a dynamic parameter.

Duration is a SUM, which gets calculated according to the value of :Mois. If I create a view, removing the :Mois parameter altogether, then SUM gets it all wrong because it calculates based on every record, not just those that are to be selected.

The results of the query is passed on to another query, and yet to another query for further calculations, and eventually a report is fired up and displays the results. It works perfectly so long as there is no data on the last day of the month, which of course there is. Maybe a workaround would be to have a piece of code re-write the query each time the report is opened, with the proper WHERE condition that I could get from a pop-up input box? But I'm surprised I can't get it done from a Base query.

[Villeroy]

Now I understand the problem. I'm out for now. No idea other than instructing the users to enter the next day.

[Sliderule]

You wrote the code below, and, you indicated it works ( of course, the @Mois and INTERVAL 1 DAY is an issue with the OpenOffice / LibreOffice Base Parser ).

Code: Select all

SELECT 
        Act.ClientID,
        Act.Start,
        Cat.Category,
        SUM(TIME_TO_SEC(TIMEDIFF(End, Start))) AS Duration,
        Act.End
    FROM
        x_pect.Category Cat,
        x_pect.Activity Act
    WHERE
        Cat.CategoryID = Act.CategoryID
      AND Act.End < @Mois + INTERVAL 1 DAY
    GROUP BY Act.ClientID , Cat.Category
    ORDER BY Act.ClientID ASC

While I have / use on occasion, MySQL . . . BUT, in my database, I have in separate columns, the DATE and TIME values. Therefore, I canNOT test this for sure, but, please try the following:

Code: Select all

SELECT 
        Act.ClientID,
        Act.Start,
        Cat.Category,
        SUM(TIME_TO_SEC(TIMEDIFF(End, Start))) AS Duration,
        Act.End
    FROM
        x_pect.Category Cat,
        x_pect.Activity Act
    WHERE
        Cat.CategoryID = Act.CategoryID
      AND Act.End < ADDDATE(LAST_DAY(STR_TO_DATE(:Enter_Date_as_m/d/yy,'%m/%d/%Y')),1)
    GROUP BY Act.ClientID , Cat.Category
    ORDER BY Act.ClientID ASC

Explanation: The WHERE clause is using the MySQL functions:

1. STR_TO_DATE Used to define the FORMAT that a date will be input, change to how you need it:
   1. The % sequences
   2. The use of / or - or . as the format input string
2. :Enter_Date Used to create the Parameter ( Prompt ) pop-up for the user
3. LAST_DAY Used to calculate the last day of the month from the input date
4. ADDDATE Used to increase the date by one to handle the time portion of Act.End

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[MrEgg964]

This is not working: entering the WHERE condition as suggested returns an error saying that this is not a valid date format.

I have been using this database for over a year, and never had this problem in the past. I went to check my monthly PDF reports from last year, and indeed back then the <= :Mois condition was working perfectly on my DATETIME field. If I generate today the PDF for that same period from last year, I now get a wrong calculation.

It would appear the problem started in July this year. I am getting regular LO updates. Could it be due to one of those? I am using LO 4.4.2.2 on one machine (stable) and LO 5 on another (with oracle report builder being very unstable), and I get the <= problem on both.

[Villeroy]

The M/D/Y date is not a valid date format for a :named_parameter unless your office locale is US English (Tools>Options>LanguageSettings>Languages>Locale). IMHO, D/M/Y is valid for all the non-US locales and Y-M-D (ISO) should work in any case.

[Sliderule]

Please forgive me, in my prior post . . . the portion in the WHERE clause to define the prompt . . . contained special characters ( / ) that is not valid. Therefore, please try the following, and, enter it in D/M/YY format, such as:

1. 22/9/15
2. 22/9/2015
3. 22/09/15
4. 22/09/2015

Code: Select all

SELECT 
        Act.ClientID,
        Act.Start,
        Cat.Category,
        SUM(TIME_TO_SEC(TIMEDIFF(End, Start))) AS Duration,
        Act.End
    FROM
        x_pect.Category Cat,
        x_pect.Activity Act
    WHERE
        Cat.CategoryID = Act.CategoryID
      AND Act.End < ADDDATE(LAST_DAY(STR_TO_DATE(:Enter_Date,'%d/%m/%Y')),1)
    GROUP BY Act.ClientID , Cat.Category
    ORDER BY Act.ClientID ASC

Explanation: I have tested this with OpenOffice Base Version 4.1.0 and LibreOffice Base Version 4.3.7.2 .

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[MrEgg964]

Nope, I'm still getting an error. I can only validate the expression in the SQL editor, not in the Design View (where the WHERE expression returns an error). There is still a problem, because if I run the query form the SQL editor, it will return results as expected (Duration is correct). However, if I run my reports, which is based on a number of cascading queries (among which this one), it gets it wrong -- and I'm only halfway surprised, since it did tell me that there was a problem with the SQL expression when I tried to save the query.

Sliderule, if this works for you in LO 4.3.7.2, then could it be that I have a problem with my own version of LO? I'm really starting to get suspicious here, especially due to the fact that, again, this whole thing had never giving me a headache prior to June 2015.

[Sliderule]

At this point, I really do not know. Remember, with the MySQL tables I am using, the database table columns are two, one is a DATE, the other is a TIME, whereas, yours is a TIMESTAMP. Not sure if this makes a difference.

In your report, you must be sure, you are NOT running it ( the Select statement(s) ) in DIRECT mode ( withOUT the Base Parser ), since, you want the user to enter ( be prompted ) for a date.

Sliderule

[MrEgg964]

What I've done so far, because after everything I do need to get my work done, is to have the report re-write the query with the proper date (:Mois + INTERVAL 1 DAY) and that works. For the query to be written properly, I'm sending an InputBox to the user, asking them to enter the date (last day of the month they want to get a report for), and then the rest of the queries send the user another pop-up asking them for :Mois, which is again the last day of the month. ':Mois' is the only parameter that is used by the series of queries, so it only gets asked once by these.

To your knowledge, is there a way to somehow send the queries the result of the InputBox, so that the user gets asked only once?

This is a lot of work for something that used to be just fine, I'm telling you

[Sliderule]

MrEgg964:

I am surprised ( I think ) by what you say you wrote as a Query. Let me attempt to explain a few things . . .

A Query, is FIRST processed ( edited ) by OpenOffice / LibreOffice Base . . . to check some syntax ( key-words ) in the Query. That includes, WHEN, a Parameter Query ( :Text_Prompt ) is used to have the pop-up and edit the SQL so what is sent to the database back-end is correct ( for example, to put a DATE in the correct syntax ( YYYY-MM-DD ), or, to add single quotes around text strings ).

To the best of my knowledge, a few key-words are NOT supported ( allowed ) by the Base Parser, that might be valid with SQL statements, including, the keyword INTERVAL , and using DATE as a function name .

Now, I would like you to try, one more time, the following Query, on your system ( slightly changed from what I wrote above ) . . . and . . . if it works ( prompts and returns the correct data ) . . . you can SAVE it as a Query, even though, NO, you canNOT edit it with the GUI ( Create Query in Design View... ), BUT you CAN edit it with Edit in SQL View... while still running it with the Base Parser .

The idea here, is to make your TIMESTAMP column a 10 character text string as a date in YYYY-MM-DD format, AND, the same for the user input.

Code: Select all

SELECT 
        Act.ClientID,
        Act.Start,
        Cat.Category,
        SUM(TIME_TO_SEC(TIMEDIFF(End, Start))) AS Duration,
        Act.End
    FROM
        x_pect.Category Cat,
        x_pect.Activity Act
    WHERE
        Cat.CategoryID = Act.CategoryID
      AND CAST(Act.End as CHAR(10)) < ADDDATE(LAST_DAY(STR_TO_DATE(:Enter_Date,'%d/%m/%Y')),1)
    GROUP BY Act.ClientID , Cat.Category
    ORDER BY Act.ClientID ASC

The above code, will require a user to enter the date, with the parts separated by a divide sign ( / ) in a DD/MM/YY or DD/MM-YYYY format ( you can change to / to a dash ( - ) if you like, your choice.

Explanation: Remember, you can save it, and open it, but, NOT with the GUI.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[MrEgg964]

I'm sorry, you're absolutely right, the code that I wrote which worked was not the one I described in my post above. My mistake. I tried different things, and I got confused.

Here's the code that works:

Code: Select all

InputDate = InputBox("Date du 1er du mois suivant, au format yyyy-mm-dd", "Calcul du total par catégorie", "2015-01-01")
strSQL = "SELECT `Act`.`ClientID`, `Act`.`Start`, `Cat`.`Category`, SUM( TIME_TO_SEC( TIMEDIFF( `End`, `Start` ) ) ) `Duration`, "
strSQL = strSQL & "`Act`.`End` FROM `x_pect`.`Category` `Cat`, `x_pect`.`Activity` `Act` WHERE `Cat`.`CategoryID` = `Act`.`CategoryID` "
strSQL = strSQL & "AND `Act`.`End` < {d '" & InputDate & "' } GROUP BY `Act`.`ClientID`, `Cat`.`Category` ORDER BY `Act`.`ClientID` ASC"

oQuery = ThisComponent.Parent.DataSource.getQueryDefinitions().getByName("qrycalcTotalByCategory")
oQuery.Command = strSQL

This version is quite confusing because in order to get a report for July 2015, the user must first enter 2015-08-01 (which is for the INTERVAL equivalent statement), then 31/07/2015 for :Mois (which is triggered by other queries) -- two different dates, two different formats. That can only be temporary, because this is ugly and confusing. But at least it works, and the reports are correct -- just like the way they used to be before whatever changed, changed. I think at one point I will try to use an older version of LO, just to see whether the problem could be coming from there.

The code you suggested does work, but then creates other errors elsewhere on the report, whereas the code I wrote above works throughout the entire report. It's not your fault, you don't have the whole picture of how the report works, with all the queries that are involved. But still, I have to fix the double input from the user, the two dates that must be entered, and the two different date formats that are currently being used.

Right now, I'm at the point where I think I'll have to generate the queries from code only, like the code I wrote above, for each of the queries that are being used to create the report. If I do that, I suppose I could put a text zone for the date of the report, on the form from which the report is triggered through the push of a button.

[Sliderule]

But still, I have to fix the double input from the user, the two dates that must be entered, and the two different date formats that are currently being used.

Well, how about this, in the code I wrote above . . . the Base Parser, does accept ( works using ) the MySQL functions:

1. ADDDATE . . . to add ( or subtract a negative number ) from a given date
2. LASTDAY . . . to get the last day of the month for any given date

So, perhaps are able to use only ONE user input, and, use that, with the above functions, to get what you need. Just a thought.

Warning, Sliderule thinking, or, attempting to think may be hazardous to your health.

Sliderule

[Villeroy]

Code: Select all

InputDate = InputBox("Date du 1er du mois suivant., "Calcul du total par catégorie", "01/01/2015")
on error goto invalidDate
  DateVal = cDate(InputDate)
  InputDate = format(DateVal, "yyyy-mm-dd")
on error goto 0
strSQL = ....

exit sub
  invalidDate:
Msgbox "No date or invalid date given. Exit", 16, "Macro Canceled"
End Sub