[Solved] Base form or Query with calculated fields

Creating tables and queries
Post Reply
Debbie7
Posts: 5
Joined: Mon Mar 10, 2008 11:03 pm

[Solved] Base form or Query with calculated fields

Post by Debbie7 »

Hiya All
My name is Debbie, I'm a Community Nurse here the UK,

I'm new to o0o and with the help of Dai's tutorials found here <http://showmedo.com/videos/?author=1922> (Thanks Dai !)
been trying to set up a database for my "Daily Call Logs" with Base.
However I'm totally lost about how to "total" the time spent with each client automatically and keep a running total of time worked each month.

I have tried to set up a query taking the data from "end time" minus "start time" and setting up a new column for the resulting data which i read on this thread: http://user.services.openoffice.org/en/ ... f=13&t=955 but keep getting error msgs.

I have had to export the data to a spreadsheet to get the results for the last couple of weeks until i can nail this.

Here are the fields I've set up

1. ID ..................................(primary Key = No input)
2. Date ...............................(Input Date)
3. Start Time .......................(Input Time)
4. End Time ........................(input Time)
5. Clients Name ..................(input Name)


Thanks in advance
Debbie :-)

PS. I'm not dumb & willing to learn.

open office v. 2.3.1
Sun Builder Ext. (which is really lost me)
Win XP sp2
Last edited by Debbie7 on Fri Mar 14, 2008 12:58 am, edited 1 time in total.
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Base form or Query with calculated fields

Post by Sliderule »

Debbie7:

I will assume, you are using HSQL as your database. HSQL is the 'default' database in OpenOffice Base . . . and, you can confirm this when you open your database, if, on the status bar at the bottom, it says: "Embedded database" and "HSQL database engine".

You have asked . . .
Debbie7 wrote:I have tried to set up a query taking the data from "end time" minus "start time" and setting up a new column for the resulting data
Just so you understand . . . with the current release of HSQL, Date and Time 'arithmetic' is not applicable directly, as you have attempted.

But, there is a work-around to accomplish what you want. I will assume, per your comment . . . since you have defined your table for "Start Time" and "End Time" with Type TIME . . . this is one way to accomplish the task.

In BASE . . . a function exists named: DATEDIFF . You can see some 'documentation' on DATEDIFF at: http://wiki.services.openoffice.org/wik ... _Functions . As documented,
http://wiki.services.openoffice.org/wiki/Built-in_functions_and_Stored_Procedures#Date_and_Time_Functions wrote:
DATEDIFF(string, datetime1, datetime2)


Returns the count of units of time elapsed from datetime1 to datetime2. The string indicates the unit of time and can have the following values 'ms'='millisecond', 'ss'='second','mi'='minute','hh'='hour', 'dd'='day', 'mm'='month', 'yy' = 'year'. Both the long and short form of the strings can be used.

DATEDIFF('dd', '2007-08-01', '2007-09-01' ) = 31
Please note above . . . that both, datetime1 and datetime2 above, because you are using "time" calculations . . . must include both a DATE and a TIME, in the form YYYY-MM-DD HH:MM:SS .

I will further assume, that you want to CALCULATE the time spent . . . for example . . .

Code: Select all

Start Time       End Time             Result
9:00                15:00           6.000000
9:15                15:00           5.750000
9:45                15:15           5.500000
9:00                15:13           6.216667
9:10                18:23           9.216667
OK, so, to perform the 'calculation' identified as "Result" above . . . when creating your Query . . . copy the 'code' below into a "Field" in your Query, and, define an "Alias" name.

Code: Select all

DATEDIFF('minute', CONCAT( CONCAT("Date", ' '), "Start Time"), CONCAT(CONCAT("Date", ' '), "End Time") ) / 60.00000
Yes, it is 'long' . . . and . . . appears very complicated, but, it is not that bad. Just so you know . . . what is being done:
  1. Calculate the differnece, in minutes, between "Start Time" and "End Time". We have to CONCATENATE the "Date" and a SPACE to the two TIMES above.
  2. With the minutes calculated, DIVIDE the minutes by 60.00000 . . . to return HOURS.MINUTES as a FRACTION.
In the above . . . you may change the FIELD NAME of "DATE", "START_TIME", and "END_TIME" to whatever 'exactly' matches your database definitions.

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

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Debbie7
Posts: 5
Joined: Mon Mar 10, 2008 11:03 pm

Re: Base form or Query with calculated fields

Post by Debbie7 »

Hiya Sliderule,
Thank you so much for answering. :-)


Here is my Table, note, the last two fields set for "No input" thinking/hoping they would be populated from calculated data of "Start & End_Times".
MY_TABLE.jpg
This my Query selecting calls made between 1st-7th March.
MY_QUERY.jpg
I tried to put your code in under the "ALLOCATED_TIME" column with errors being returned, then I read your instructions again and put it in a new field with an Alias of "Results" and got this back.
capture_13032008_204142.jpg
Shall I take the "Date" out of the code??

Debbie7 :-)
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Base form or Query with calculated fields

Post by Sliderule »

Debbie7:

Your ORIGINAL post indicated:
Debbie7 wrote:1. ID ..................................(primary Key = No input)
2. Date ...............................(Input Date)
3. Start Time .......................(Input Time)
4. End Time ........................(input Time)
5. Clients Name ..................(input Name)
So, I used your names in the 'formula' below:

Code: Select all

DATEDIFF('minute', CONCAT( CONCAT("Date", ' '), "Start Time"), CONCAT(CONCAT("Date", ' '), "End Time") ) / 60.00000
BUT . . . as noted in my comment . . .
Sliderule wrote:In the above . . . you may change the FIELD NAME of "DATE", "START_TIME", and "END_TIME" to whatever 'exactly' matches your database definitions.
It would appear the 'exact' name of your 'fields' are:
  1. DATE
  2. START_TIME
  3. END_TIME
Therefore . . . the formula should be:

Code: Select all

DATEDIFF('minute', CONCAT( CONCAT("DATE", ' '), "START_TIME"), CONCAT(CONCAT("DATE", ' '), "END_TIME") ) / 60.00000
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Debbie7
Posts: 5
Joined: Mon Mar 10, 2008 11:03 pm

Re: Base form or Query with calculated fields

Post by Debbie7 »

Sorry Sliderule

I realised that just after sending last post that I should change the field names, which has worked a treat.

Thank you very much.

Debbie7 :-)
Debbie7
Posts: 5
Joined: Mon Mar 10, 2008 11:03 pm

Re: [Solved] Base form or Query with calculated fields

Post by Debbie7 »

I feel I should show the Query below for others to see.
CALL_TIME.jpg
Thanks Sliderule

Debbie7
VickyBell747
Posts: 1
Joined: Fri Dec 12, 2008 1:58 pm

Re: [Solved] Base form or Query with calculated fields

Post by VickyBell747 »

Hello! One quick question on this topic......... the date calculates in decimals, example.......Difference been 09:00 and 10:30 is 1.5. How can I change the format to 1hour 30 mins (1:30)?? If I change the format on the query display from number to time, it doesn't appear correctly.

Thanks :-)
OOo 3.0.X on Ms Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Base form or Query with calculated fields

Post by Villeroy »

VickyBell747 wrote:Hello! One quick question on this topic......... the date calculates in decimals, example.......Difference been 09:00 and 10:30 is 1.5. How can I change the format to 1hour 30 mins (1:30)?? If I change the format on the query display from number to time, it doesn't appear correctly.

Thanks :-)
Quick shot without testing:
Get the difference in unit 'day' and format that as time. At least this is how it works in a spreadsheet where all numbers are in unit "Day" when you format them as dates or times.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
practicalcode
Posts: 6
Joined: Thu Feb 17, 2011 9:00 pm

[Solved!] Base form or Query with calculated fields

Post by practicalcode »

Because the two topics are related, I'm posting a link to a detailed question/write-up. In the following topic...

Re: (UN)Solved Concatenation rules in query fieldname field
http://user.services.openoffice.org/en/ ... 06#p178706

I stipulate that, "It would appear there's no way to concatenate a field and a string literal in Base, connected to an ODBC Access db...!?..."

Since this original post, it has been solved by Mr. Sliderule... :super:
If you're interested in 'how' to tell Base to pass the sql directly to the ODBC source esp when using functions / syntax that isn't interpretable by the OpenOffice Base Parser, pls read his followup post:
http://user.services.openoffice.org/en/ ... 69#p178714
George

LibreOffice 3.3.0 on Windows 7
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: [Solved] Base form or Query with calculated fields

Post by chrisb »

Hello Debbie7 & VickyBell7

WOW the original post was in March 2008.
I am sure you have found a solution to this long ago or perhaps moved on and lost interest in Base.
However as your posts have reappeared as a current topic I hope this helps both you and others .
I have included a day field in order to complete the example.
The field names are not identical to yours so you will need to adjust the query to match your needs.

First the query:-

SELECT "StartDate", "EndDate", "StartTime", "EndTime", "Client",

DATEDIFF( 'mi', CONCAT( CONCAT( "StartDate", ' ' ), "StartTime" ), CONCAT( CONCAT( "EndDate", ' ' ), "EndTime" ) ) AS "Mins",

FLOOR( DATEDIFF( 'mi', CONCAT( CONCAT( "StartDate", ' ' ), "StartTime" ), CONCAT( CONCAT( "EndDate", ' ' ), "EndTime" ) ) / 1440.0 ) AS "Days",

MOD( DATEDIFF( 'mi', CONCAT( CONCAT( "StartDate", ' ' ), "StartTime" ), CONCAT( CONCAT( "EndDate", ' ' ), "EndTime" ) ), 1440 ) / 60 AS "Hrs",

MOD( DATEDIFF( 'mi', CONCAT( CONCAT( "StartDate", ' ' ), "StartTime" ), CONCAT( CONCAT( "EndDate", ' ' ), "EndTime" ) ), 60 ) AS "Mins"

FROM "tDailyLogCalls"

And the result:-
Attachments
DailyLogCalls.PNG
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Post Reply