LO Base | Calculating Average After Calculating DATEDIFF

[JuanCaballo]

I’m quite new at SQL and databases in general, so please be patient with me.
I have searched for a solution to this for days now. I have tried all sorts of possibilities, nothing.
The table I use has, among others, the following date columns:

job1_start, job1_end, job2_start, job2_end, job3_start, job3_end

I have been able to calculate the amount of months for each job with:

SELECT
  DATEDIFF('day' "table"."job1_start", "table"."job1_end") / 365.00 * 12 AS job1_months
  DATEDIFF('day' "table"."job2_start", "table"."job2_end") / 365.00 * 12 AS job2_months
  DATEDIFF('day' "table"."job3_start", "table"."job3_end") / 365.00 * 12 AS job3_months
FROM "table"

(I use the ~/ 365.00 * 12~ in order to get decimals. So e.g. 29 days is 0.96 months, not 0)

From job1_months, job2_months, job3_months I would like to calculate the average number of months the worker stays at a job. Not dividing the total by 3, since some workers might only ever have had 1 or 2 jobs. So, maybe using AVG because it ignores NULL values?

I would be very thankful if someone could help me with this.
I use HSQLDB.

[RobertG]

SELECT "workerID", AVG("job_months") AS "Average" FROM
(SELECT "workerID",
  DATEDIFF('day', "table"."job1_start", "table"."job1_end") / 365.00 * 12 AS job_months
FROM "table"
UNION ALL
SELECT "workerID",
  DATEDIFF('day', "table"."job2_start", "table"."job2_end") / 365.00 * 12 AS job_months
FROM "table"
UNION ALL
SELECT "workerID",
  DATEDIFF('day', "table"."job3_start", "table"."job3_end") / 365.00 * 12 AS job_months
FROM "table") AS "a"
GROUP BY "workerID"

Try this code. You have to execute in SQL-Mode directly, because the query GUI doesen’t know anything about UNION.
But you will need another field (worker?) to group this query. The code will only give one value.
Edit: Have added a field “workerID” to group this query.

[cpb]

@JuanCaballo,
you can use DATEDIFF to calculate the months:

DATEDIFF('mm' "table"."job1_start", "table"."job1_end") AS job1_months

there is 1 caveat:
DATEDIFF(‘mm’, ‘2024-01-05’, ‘2024-10-01’) returns 9 when in fact the number of whole months is 8, we can fix that anomaly using SQL, see query “qJobHistoryMonths_sql”.
.
it looks like you add a column to your table every time you start a new job, that’s ok for a spreadsheet but not for a database.
you have 3 columns and 1 row whereas a database would have 1 column and 3 rows.
.
for a simple example download the attachment.
EDIT: sorry forgot to do the average months, have now added the query “qAverageMonths”
EDIT: replaced attachment
Jobs2.odb (15.2 KB)

[JuanCaballo]

I created a table named “table” and exactly the fields as mentioned above, double and triple checked that all was written properly, but doing the direct SQL query gives the following:

1: Unexpected token in statement [SELECT AVG(“job_months”) AS “Average” FROM
(SELECT
DATEDIFF(‘day’ “table”.“job1_start”] at /home/abuild/rpmbuild/BUILD/libreoffice-24.2.3.2/connectivity/source/drivers/jdbc/Object.cxx:173

And yes, I have a field for the ID that I could group by.

[RobertG]

Please upload an example database to see the error. Have only copied your code into three different queries and combined it to one field.
There is missing comma after ‘day’. Will correct this.

[cpb]

@JuanCaballo,
when you post code as in your first post then contributors will assume that the code is valid and consequently may copy and paste that code in any response, the code you posted is invalid a comma is required after ‘day’.
.
the issue with DATEDIFF and months is only apparent when dayofmonth(“StartDate”) is greater than dayofmonth(“EndDate”).
.
I have replaced the original attachment with Jobs2.
I have updated the queries and added a query called “qEmployeeTenure_sql” which shows months plus days (i.e. 52 Months 27 Days).

[JuanCaballo]

@cpb , @RobertG ,
Thank you very much for your solutions. You helped me a lot with understanding databases, and yes, I’m still thinking too much in terms of spreadsheets.

I’m very sorry for omitting the commas.

[JuanCaballo]

@RobertG,
Could you please help me grouping this query? The field would be workerID.
I have tried and tried, but nothing worked.
Thank you

[RobertG]

@JuanCaballo

Could you please help me grouping this query?

Which query do you mean? This thread is solved since 24 days.

[Wanderer]

@RobertG

Which query do you mean?

I guess the query in your first comment, as you wrote there:

@RobertG

But you will need another field (worker?) to group this query.

[RobertG]

Have changed code in the first thread to group by “workerID”.