How to word a parameter input query in Base using Mariadb to select a date range?

rherbert · July 2, 2023, 11:32am

Thank you @parsely.

I have sanitized the data to protect personal information. Find it attached.

TimeQryTest.odb (202.2 KB)

Time dates best to use for data span July and August 2022. I have attached a current working version of the entire relational database in case that is germane. There are minor differences with data I have included here.

FYI, none of the work identified in this data was billed to indigenous people. In fact, my work was donated (unfunded). Projects this data came from can be found at Recent Projects.

Thank you in advance.
Richard

parsely · July 2, 2023, 7:33pm

You are dealing with dates, a difficult datatype.
There is a DATEDIFF function, unclear to me whether MariaDB supports this, otherwise replace by an equivalent.
There is a correction for StopTimes after 24:00.
Instead of start/end dates you can consider month, that is a more distinct interval
I have added a field Hourly Rate to differentiate between projects
There is a Number column, in calc a sequence number column must be added (based on last invoice in SAGE), to comply with good bookkeeping standards.
Use an odt file with fields (insert field > more fields > database selection >…) finally insert a next record field. With CTRL+P the invoices can be generated.
Hope that LOcalc generating .xlsx output is compatible with SAGE, otherwise consider a CSV output.

TimeQryTestV1.odb (213.1 KB)

rherbert · July 2, 2023, 9:56pm

Thank you for your help @parsely!

The SQL code for your billing queries did not work in Mariadb as you suspected. The error I had was:

Screenshot_20230702_181343

The queries you sent did work at my end with the HSQLDB that you sent them back in. I have noted in past situations that there are differences with SQL between HSQLDB and Mairadb. Mariadb has not been a good resource for SQL differences when using LO Base. I have had some help from this forum (thank you to those people), one of those was @Wanderer. Regardless, I need to know who to connect with and where to get information to resolve these syntax differences.

Any thoughts on where to get information or whom to contact? I am reluctant to post/share your code as it is yours.

Thanks
Richard

Wanderer · July 2, 2023, 10:09pm

Start with posting the query from sql-view here, so we know, wich query fails. Some of us also work with MariaDB/MySQL and may have a look.
.
Usually I would suggest to switch to direct mode for SQL first, if I read “query could not be parsed”, but as it seemed to work with HSQLDB, I’m not convinced it will help in this case.

rherbert · July 2, 2023, 10:28pm

Hi @Wanderer. Thank you for responding.

Two comments back queries with test data were sent in an odb file:

TimeQryTestV1.odb (213.1 KB)

@parsely, may I post your code directly here?

Richard

Wanderer · July 2, 2023, 10:32pm

I can read sql also on my mobile, testing an odb will have to wait until I have time with my desktop…
.
And I assumed there is more than one query, so I/we have to find the place with the error, while you already know where it occurs.

rherbert · July 2, 2023, 10:40pm

I will go with the sharing of code on this forum with the odb file permits sharing with a copy and paste. The two qeiries are:

“Basic Billing”

SELECT "tbl_Organization"."Organization_ID",
 "tbl_File"."File_ID", "tbl_Time"."ToDo_ID", "tbl_Time"."Date", "tbl_Time"."Start_Time",
 "tbl_Time"."Stop_Time",
 CASEWHEN( DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0, DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ), 1440 - DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" ) ) AS "Difference",
 CASEWHEN( DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0, DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ), 1440 - DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" ) ) * "Hourly Rate" / 60 AS "Invoice",
 RIGHT( CONCAT( '000', "File_ID" ), 3 )  AS "number",
 "tbl_File"."Project_Title",  "tbl_Individual"."Name",
 "tbl_Organization"."Name",  "tbl_Organization"."Address"
 FROM "tbl_Time", "tbl_Organization",  "tbl_ToDo", "tbl_Individual",  "tbl_File"
 WHERE "tbl_Time"."Organization_ID" = "tbl_Organization"."Organization_ID"
 AND "tbl_Time"."ToDo_ID" = "tbl_ToDo"."ToDo_ID"
 AND "tbl_ToDo"."Individual_ID" = "tbl_Individual"."Individual_ID"
 AND "tbl_ToDo"."File_ID" = "tbl_File"."File_ID"
 AND "tbl_Time"."Date" > :start
 AND "tbl_Time"."Date" < :stop

“Grouped Billing”

SELECT "tbl_Organization"."Organization_ID" AS "OrganizationID",
  MIN( "tbl_File"."File_ID" ) AS "FileID",
  COUNT( "tbl_ToDo"."ToDo_ID" ) AS "Count",
  SUM( CASEWHEN( DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0, DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ), 1440 - DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" ) ) ) AS "Minutes",
  SUM( CASEWHEN( DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0, DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ), 1440 - DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" ) ) * "Hourly Rate" / 60 ) AS "Invoice",
  MIN( CONCAT( RIGHT( CONCAT( '000', "Organization_ID" ), 3 ), RIGHT( CONCAT( '000', "File_ID" ), 3 ) ) ) AS "number",
  MIN( "tbl_File"."Project_Title" ) AS "ProjectTitle",
  MIN( "tbl_Organization"."Name" ) AS "NameOrganization",
  MIN( "tbl_Organization"."Address" ) AS "Address"
  FROM "tbl_Time", "tbl_Organization", "tbl_ToDo", "tbl_Individual", "tbl_File"
  WHERE "tbl_Time"."Organization_ID" = "tbl_Organization"."Organization_ID"
  AND "tbl_Time"."ToDo_ID" = "tbl_ToDo"."ToDo_ID"
  AND "tbl_ToDo"."Individual_ID" = "tbl_Individual"."Individual_ID"
  AND "tbl_ToDo"."File_ID" = "tbl_File"."File_ID"
  AND "tbl_Time"."Date" > :start
  AND "tbl_Time"."Date" < :stop
  GROUP BY "tbl_Organization"."Organization_ID"

Thanks for looking at this.
Richard

parsely · July 3, 2023, 4:31am

DATEDIFF and CONCAT are OK with MariaDB, I suspect CASEWHEN must be replaced by a simple IF as documented built-in-functions

The invoice sequence number is a critical item, it can be easily added in a spreadsheet and hard in SQL?. In hindsight the composition of invoices must then be based on the spreadsheet. insert field > more fields > database Tab > add datasource > select the spreadsheet > and so on
With this approach only the sum of all projects for one organisation can be presented and no specification line showing each project.

Wanderer · July 3, 2023, 5:22am

I would start there also, but expect the “traditional” command CASE WHEN … THEN … ELSE … END; as syntax.

CASE WHEN DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0 THEN DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) ELSE 1440 - DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" )  END AS "Minutes"

Villeroy · July 3, 2023, 10:28am

Read up the documentation of your database engine. DATEDIFF is defined somewhat different: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_datediff
In My/MariaSQL uses a function TIMESTAMPDIFF similar to HSQL’s DATEDIFF.

rherbert · July 3, 2023, 11:48am

Thank you @parsely, @Wanderer, and @Villeroy.

I will begin getting through I note that I have come across quotation differences between HSQLDB and Mariadb that are likely old hat for you. Examples are:

With a find and replace
HSQLDB
UPDATE "tbl_reference"
 SET "Link" = REPLACE("Link", 'home/rherbert/Documents', 'mnt/CAID')
Mariadb
UPDATE tbl_reference
 SET Link = REPLACE(Link, 'home/rherbert/Documents', 'mnt/CAID')

and with a list box query
HSQLDB
SELECT "Category", "Category_ID"
 FROM "tbl_category" 
Mariadb
SELECT `Category`, `Category_ID`
 FROM `Library`.`tbl_category` `tbl_category`

I shall attempt to convert this and get back to everyone.

Thank you,
Richard

rherbert · July 3, 2023, 12:06pm

Thank you @Villeroy. It note that mariadb supports both DATEDIFF and TIMESTAMPDIFF, but this provides other routes to explore if necessary.

rherbert · July 3, 2023, 1:51pm

@Wanderer, you wrote:

CASE WHEN DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0 THEN DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) ELSE 1440 - DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" )  END AS "Minutes"

I have observed that SQL in mariadb handles double quotations differently. In some instances they are dropped and in others replaced by " ` " = "grave accent’. How does that effect what you wrote and does it affect the “RIGHT( CONCAT …” line?

Thanks,
Richard

Wanderer · July 3, 2023, 4:01pm

Quotes are not related to the question of CASEWHEN()-function versus statement-syntax CASE WHEN … THEN …

On quoting check the link to a thread thread at stackoverflow; You can often drop quotes when you have simple column-names like Price, Name. You need double quotes with spaces or special characters and single quotes for all string values. Backticks I only use when I put SQL in Text-Strings in Basic or other programming languages.

rherbert · July 3, 2023, 4:25pm

Hi @parsely,

I adjusted quotations to reflect SQL with mariadb (not certain if I have all corrected) and I followed your direction and changed CASEWHEN to IF. What I tried was:

SELECT `tbl_Organization`.`Organization_ID`,
 `tbl_File`.`File_ID`,
 `tbl_ToDo`.`ToDo_ID`,
 `tbl_Time`.`Date`,
 `tbl_Time`.`Start_Time`,
 `tbl_Time`.`Stop_Time`,
 IF( DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) > 0, DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ), 1440 - DATEDIFF( 'MINUTE', `Stop_Time`, `Start_Time` ) ) `Difference`,
 IF( DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) > 0, DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ), 1440 - DATEDIFF( 'MINUTE', `Stop_Time`, `Start_Time` ) ) * `Hourly Rate` / 60 `Invoice`,
 RIGHT( CONCAT( '000', `File_ID` ), 3 ) `Number`,
 `tbl_File`.`Project_Title`,
 `tbl_Individual`.`Name`,
 `tbl_Organization`.`Name`,
 `tbl_Organization`.`Address`,
 `tbl_Time`.`Date`
 FROM `CAID`.`tbl_ToDo` `tbl_ToDo`,
 `CAID`.`tbl_Individual` `tbl_Individual`,
 `CAID`.`tbl_Time` `tbl_Time`,
 `CAID`.`tbl_Organization` `tbl_Organization`,
 `CAID`.`tbl_File` `tbl_File`
 WHERE `tbl_ToDo`.`Individual_ID` = `tbl_Individual`.`Individual_ID`
 AND `tbl_Time`.`ToDo_ID` = `tbl_ToDo`.`ToDo_ID`
 AND `tbl_Time`.`Organization_ID` = `tbl_Organization`.`Organization_ID`
 AND `tbl_ToDo`.`File_ID` = `tbl_File`.`File_ID`
 AND `tbl_Time`.`Date` > :Start
 AND `tbl_Time`.`Date` < :Stop

The parsing error was resolved and there are not SQL syntax errors. I am able run the query and enter start/stop date parameters but then the following error occurs:

Screenshot_20230703_113447

I also put back CASEWHEN and got to the same point without an SQL error. I am uncertain what that indicates. I then experimented with some of @Wanderer 's suggestions but had consistent syntax errors.

What is missing? (I did put an Hourly Rate field in my ToDo table for the field you added to the test data set.)

Thank
Richard

parsely · July 3, 2023, 7:43pm

Hi Herbert,

It is certain that I can not be of assistance with things related to MariaDB, I put my hope to others reading the post

CASEWHEN is not supported by MariaDB, so use the IF(logical test, expressionTrue, expressionFalse) function.

You can click ‘more’ to check whether that gives you a hint of what is goiing on, as a last resort you can comment out all columns but one, to get rid of errors from complex expressions, and than rebuild the SQL. You can comment out text like this: /* this will be ignored by the SQL parser */ Beware of trailing/double commas in that case

rherbert · July 3, 2023, 8:39pm

Hi @parsely,

I understand your limit with mariadb. You have been a great help. I did not have a chance to thank you for the macro to export a query to LO Calc (.ods) - awesome. One question though on the macro, why create a Log table and use that with the button?

Thanks again,
Richard

rherbert · July 3, 2023, 9:38pm

Hi @Wanderer,

Thank you for the link on use of quotes with MySQL. I needed a resource to sort that out. As for CASE versus IF with my billing queries. As a beginner with SQL, it seemed to me that either could work. But, I shall default to your expertise and put my effort into CASE. There is CASE Operator and CASE Statement. after reading through descriptions, it seems CASE Operator is what I am looking for. I tried a number of combinations and received a syntax error for SQL.

Screenshot_20230703_181907

Combinations I tried, starting with your suggestions, are:

CASE WHEN DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) > 0 THEN DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) ELSE 1440 – DATEDIFF( 'MINUTE', "Stop_Time", "Start_Time" ) END AS "Difference"

CASE WHEN DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) > 0 THEN DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) ELSE 1440 – DATEDIFF( 'MINUTE', `Stop_Time`, `Start_Time` ) END AS `Difference`

CASE `Difference` WHEN DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) > 0 THEN DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) ELSE 1440 – DATEDIFF( 'MINUTE', `Stop_Time`, `Start_Time` ) END

CASE WHEN DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) > 0 THEN DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) ThEN 1440 – DATEDIFF( 'MINUTE', `Stop_Time`, `Start_Time` ) END AS `Difference`

CASE `Difference` WHEN  DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) > 0 THEN DATEDIFF( 'MINUTE', `Start_Time`, `Stop_Time` ) THEN 1440 – DATEDIFF( 'MINUTE', `Stop_Time`, `Start_Time` ) END

There is no doubt I am doing something wrong, but what?

Richard

cpb · July 3, 2023, 11:39pm

@rherbert,
’
are you saying that your actual query is failing when executed in the query design window?
if so try this:

SELECT
	`tbl_Organization`.`Organization_ID`, 
	MIN( `tbl_File`.`File_ID` ) `FileID`, 
	COUNT( `tbl_ToDo`.`ToDo_ID` ) `Count`,
	SUM
	( 
		CASE
			WHEN `Stop_Time` > `Start_Time` then
				cast((time_to_sec(`Stop_Time`) - time_to_sec(`Start_Time`)) / 60 as int)
			else
				1440 - cast((time_to_sec(`Start_Time`) - time_to_sec(`Stop_Time`)) / 60 as int)
		end
	)
	AS `Minutes`,
	SUM
	( 
		CASE
			WHEN `Stop_Time` > `Start_Time` then
				(time_to_sec(`Stop_Time`) - time_to_sec(`Start_Time`)) / 60
			else
				1440 - (time_to_sec(`Start_Time`) - time_to_sec(`Stop_Time`)) / 60
		end
		* `Hourly Rate` / 60 
	) AS `Invoice`,
	MIN( CONCAT( RIGHT( CONCAT( '000', `tbl_Organization`.`Organization_ID` ), 3 ), RIGHT( CONCAT( '000', `tbl_File`.`File_ID` ), 3 ) ) ) AS `number`, 
	MIN( `tbl_File`.`Project_Title` ) `ProjectTitle`, 
	MIN( `tbl_Organization`.`Name` ) `NameOrganization`, 
	MIN( `tbl_Organization`.`Address` ) `Address`

FROM 
	`tbl_Time`, 
	`tbl_Organization`,
	`tbl_ToDo`, 
	`tbl_Individual`,
	`tbl_File` 
WHERE 
	`tbl_Time`.`Organization_ID` = `tbl_Organization`.`Organization_ID` 
	AND 
	`tbl_Time`.`ToDo_ID` = `tbl_ToDo`.`ToDo_ID` 
	AND 
	`tbl_ToDo`.`Individual_ID` = `tbl_Individual`.`Individual_ID` 
	AND 
	`tbl_ToDo`.`File_ID` = `tbl_File`.`File_ID` 
	AND 
	`tbl_Time`.`Date` > :start
	AND 
	`tbl_Time`.`Date` < :stop
GROUP BY 
	`tbl_Organization`.`Organization_ID` 
ORDER BY 
	`tbl_Organization`.`Organization_ID`

rherbert · July 4, 2023, 2:44am

Thanks for your reply @cpb.

I copy and pasted your code into LO BASE after selecting Create Query in SQL Mode. I adjusted for the name of my Mariadb database. Other changes that occurred were done so by LO Base after I saved the query and it works!!!

Here is what the code looks like after saving:

SELECT
 `tbl_Organization`.`Organization_ID`,
 MIN( `tbl_File`.`File_ID` ) `FileID`,
 COUNT( `tbl_ToDo`.`ToDo_ID` ) `Count`,
 SUM( CASE WHEN `Stop_Time` > `Start_Time` THEN CAST( ( time_to_sec( `Stop_Time` ) - time_to_sec( `Start_Time` ) ) / 60 AS INTEGER ) ELSE 1440 - CAST( ( time_to_sec( `Start_Time` ) - time_to_sec( `Stop_Time` ) ) / 60 AS INTEGER ) END ) `Minutes`,
 SUM( CASE WHEN `Stop_Time` > `Start_Time` THEN ( time_to_sec( `Stop_Time` ) - time_to_sec( `Start_Time` ) ) / 60 ELSE 1440 - ( time_to_sec( `Start_Time` ) - time_to_sec( `Stop_Time` ) ) / 60 END * `Hourly_Rate` / 60 ) `Invoice`,
 MIN( CONCAT( RIGHT( CONCAT( '000', `tbl_Organization`.`Organization_ID` ), 3 ), RIGHT( CONCAT( '000', `tbl_File`.`File_ID` ), 3 ) ) ) `number`,
 MIN( `tbl_File`.`Project_Title` ) `ProjectTitle`,
 MIN( `tbl_Organization`.`Name` ) `NameOrganization`,
 MIN( `tbl_Organization`.`Address` ) `Address`
 FROM
 `CAID`.`tbl_Time` `tbl_Time`,
 `CAID`.`tbl_Organization` `tbl_Organization`,
 `CAID`.`tbl_ToDo` `tbl_ToDo`,
 `CAID`.`tbl_Individual` `tbl_Individual`,
 `CAID`.`tbl_File` `tbl_File`
 WHERE
 `tbl_Time`.`Organization_ID` = `tbl_Organization`.`Organization_ID`
 AND `tbl_Time`.`ToDo_ID` = `tbl_ToDo`.`ToDo_ID`
 AND `tbl_ToDo`.`Individual_ID` = `tbl_Individual`.`Individual_ID`
 AND `tbl_ToDo`.`File_ID` = `tbl_File`.`File_ID`
 AND `tbl_Time`.`Date` > :start AND `tbl_Time`.`Date` < :stop
 GROUP BY `tbl_Organization`.`Organization_ID`

Thank you. Do you have insight on the other query as well?

Richard