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

[rherbert]

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]

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

[rherbert]

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

@rherbert

How does that effect what you wrote

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.

When to use single quotes, double quotes, and backticks in MySQL

[rherbert]

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:



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]

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]

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]

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.



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]

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

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

[cpb]

@rherbert,
’
the alterations are identical for both queries, use of CASE and disuse of DATEDIFF.
I don’t use MariaDB but a quick look at the manual was all it took.
both queries are the work of parsely, I simply applied the corrections required by MariaDB.
.
parameter queries must use the Base parser which severely limits your coding options.
I would suggest using a form and filter table as a superior alternative.
.
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`,
	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 `Difference`,
	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`,
	RIGHT( CONCAT( '000', `tbl_File`.`File_ID` ), 3 )  as `number`,
	`tbl_File`.`Project_Title`,  
	`tbl_Individual`.`Name`,
	`tbl_Organization`.`Name`,  
	`tbl_Organization`.`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
ORDER BY 
	`tbl_Organization`.`Organization_ID`

[parsely]

Some remarks

• It is intended as a future expansion. If you log dates you know what has been used in the past, and you can add a new record for a new billing period. A macro selects the two first records of the tblLog (sorted on date DESC) and through string manipulation inserts the dates in the query SQL string. It saves you from entering lenghty dates

• The advice from Villeroy in another post is to always use full length dates in the ISO format: 2023-07-18. Than you might already be prepared for the input SAGE expects.

SELECT ROWNUM ( ) OVER ( ORDER BY "OrganizationID_ID", "ToDo_ID" ) + 21309 
/* last invoice number in SAGE */, "S".* FROM ( <your query here> ) AS "S"

to assign a unique number to each row, in view of an invoice number SAGE expects.

• the invoice number can be concatenated with organizationID and ToDoID and used as a filename: in a folder sorted on filename you can see all invoices sent per organisation per todo

[parsely]

addendum to the Python code given in the first reply: the variable code IMPORTMODE is from an import

from com.sun.star.sheet.DataImportMode import SQL as IMPORTMODE

[rherbert]

@cpb,

It works, thank You!! And yes, I acknowledge that @parsley 's work provided a base. Again there was a couple of differences after I pasted and saved. I noted that 2 of your “as” were dropped. I include what I have on my end for completeness:
\

SELECT `tbl_Organization`.`Organization_ID`,
 `tbl_File`.`File_ID`,
 `tbl_Time`.`ToDo_ID`,
 `tbl_Time`.`Date`,
 `tbl_Time`.`Start_Time`,
 `tbl_Time`.`Stop_Time`,
 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 `Difference`,
 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`,
 RIGHT( CONCAT( '000', `tbl_File`.`File_ID` ), 3 ) `Number`,
 `tbl_File`.`Project_Title`,
 `tbl_Individual`.`Name`,
 `tbl_Organization`.`Name`,
 `tbl_Organization`.`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
 ORDER BY `tbl_Organization`.`Organization_ID` ASC

Now that I know what can be done with a query, I need to adjust my thinking for what I can include in a query before I export to LO Calc. Noteably, with Indigenous people they, without realizing, end up subsidizing resource corporations to consult with them. I need to set a few hourly rates (example chief & council, executive staff, senior staff and project staff) that feed into the Time table such that each project’s time billing (cost recovery) is totaled by project or organization (if more than one group requests consultation for the same project) each with 4 hourly rate categories (this example) that connect to staff or council that accomplished work for which cost is being recovered.

This is an interesting challenge

Thank you for your help. I also thank @parsely, @Wanderer and @Villeroy.

Richard

[rherbert]

@parsely,

Thanks again for your input. I have barely kept up with information in this Topic thread - meaning, most of this is a reach for me to understand since I have no context/experience to tie information to. In that regard, your insight on exporting to SAGE is welcomed but leaves me with more questions because I have not arrived at a point where I am working on SAGE export. At this time, Sage export is something I need to ensure can occur with what I do with time, projects and billables. However, this might be a good time to learn how to log with macros, but my macros skills are rudimentary.

At this point, I need to work on tuning billing queries and possibly modifying my database to reflect multiple hourly_rates on the same or different ToDos by organization and by project such that a final LO Calc spreadsheet output can be used as a file record of time, staff, and recoverables (billables) from which a bookkeeper can extract information for invoicing - with or without logging. After that, the next phase of exporting into SAGE for invoicing can be considered if/when the logged billing is used by an indigenous group.

Thanks for your help and please do not hesitate to set up a logging macro with the HSQLDB I sent if that is not too much of an ask.

Thanks
Richard

[rherbert]

I do not understand the context for this?

@parsely

from com.sun.star.sheet.DataImportMode import SQL as IMPORTMODE

Richard

[parsely]

This site is for questions on one specific problem, not for requests to develop (parts of) applications. That being said, here are some practical links

https://wiki.documentfoundation.org/Macros#Documentation
Andrew Pitonyak is a reference in macro circles. Most macros concern writer and calc, but there is also code for base
https://www.pitonyak.org/oo.php and https://www.pitonyak.org/database/AndrewBase.odt
Apart from operations with forms it will also concern the basics of retrieving a recordset, looping trough the set and retrieving column values.
The remark above concerns Python code, my first answer

[rherbert]

I do understand. I have learned a lot in the last 3 days. Thank you and thanks to everyone else. I have links you provided book marked and relevant documents downloaded.

Richard